postgresql count null and blank value from jsonb column

Question

In following JSON structure,

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

how to count occurrence of

1. blank value ""

2. null value

Answer 1

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.