postgresql count null and blank value from jsonb column

In following JSON structure,

[
["a", 921],
["b", ""],
..
...
["c", null],
]


how to count occurrence of

-
blank value ""

-
null value

You can use the function json_array_elements to convert your array into a set of elements. Each element is itself an array, so you can use the -> operator to retrieve the second element. Once you have them, use a standard count(*), with the corresponding FILTER.

You can do it with the following SQL statement (the WITH helps you view the "step-by-step" approach):

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

... or the following one:

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 ;

In both cases, you get:

total_elements | blank_elements | null_elements
-------------: | -------------: | ------------:
3 | 1 | 1

You can check everything at dbfiddle.

NOTE: If you want to use jsonb instead of json, just change the ::jsonwith ::jsonb in the first statement, and call the jsonb_array_elements instead of json_array_elements.

StackExchange Database Administrators Q#257794, answer score: 3