Postgresql – Count Null and Blank Values from JSONB Column

jsonpostgresql

In following JSON structure, 

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

how to count occurrence of

  1. blank value ""

  2. null value

Best Answer

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.

Related Question