1

In following JSON structure,

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

how to count occurrence of

  1. blank value ""

  2. null value

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.