Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

WITH original_data(var) AS
(
    VALUES (
    '
    [
        ["a", 921],
        ["b", ""],
        ["c", null]
    ]
    '::json)
)
, second_elements AS
(
SELECT 
    (json_array_elements(var)->1)::text AS e
FROM
    original_data
)
SELECT
    count(e) AS total_elements,
    count(e) FILTER (WHERE e = '""') AS blank_elements,
    count(e) FILTER (WHERE e = 'null') AS null_elements
FROM
    second_elements ;


total_elements
blank_elements
null_elements


3
1
1



SELECT 1


SELECT
    count(e) AS total_elements,
    count(e) FILTER (WHERE e = '""') AS blank_elements,
    count(e) FILTER (WHERE e = 'null') AS null_elements
FROM
    (
    SELECT 
        (json_array_elements(var)->1)::text AS e
    FROM
        (
        VALUES (
        '
        [
            ["a", 921],
            ["b", ""],
            ["c", null]
        ]
        '::json)
        ) AS original_data(var)
   ) AS second_elements ;
  


total_elements
blank_elements
null_elements


3
1
1



SELECT 1


WITH original_data(var) AS
(
    VALUES (
    '
    [
        ["a", 921],
        ["b", ""],
        ["c", null]
    ]
    '::jsonb)
)
, second_elements AS
(
SELECT 
    (jsonb_array_elements(var)->1)::text AS e
FROM
    original_data
)
SELECT
    count(e) AS total_elements,
    count(e) FILTER (WHERE e = '""') AS blank_elements,
    count(e) FILTER (WHERE e = 'null') AS null_elements
FROM
    second_elements ;


total_elements
blank_elements
null_elements


3
1
1



SELECT 1