Count objects in jsonb array with Postgres

Question

Assume that every jsonb value in a table have this structure:

{
  "level1": [
    {
      "level2": [
        {
          "key": key,
          "value": value,
          "messages": [

          ]
        },
        {
          "key": key,
          "value": value,
          "messages": [

          ]
        },
        {
          "key": key,
          "value": value,
          "messages": [

          ]
        }
      ]
    }
  ]
}

The names of key level1 is dynamic, so can be anything (that's why I'm using the jsonb_object_keys). I need to check if any object inside level2.messages is empty per date. That is: if all level2.messages in a date are empty, return false. Otherwise (at least one of the objects with message has a non-empty array), return true.

I thought I could use json functions in a subquery, but they are not known inside the subquery. I have something like this:

SELECT t2.date, 
(SELECT 1 FROM fields WHERE jsonb_array_length(fields ->> 'messages') = 1 LIMIT 1) AS hasMessages
FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.id,
jsonb_object_keys(t1.result) AS rootNode,
jsonb_array_elements(t1.result -> rootNode) AS level2,
jsonb_array_elements(level2 -> 'level2') AS fields
GROUP BY t2.date

Please always start with your version of Postgres. Among other things, the arsenal of available tools depends on it. As well as a table definition for table1 and table2 - along with an explanation for both tables. Or remove table2 from your question. — Erwin Brandstetter

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2019-02-19T18:25:02

Based on the fragmentary info in the question, this would work:

SELECT date
     , count(*) AS message_count
     , count(*) FILTER (WHERE l2_val->'messages' = '[]') AS empty_message_count
FROM   table1 t1
     , jsonb_object_keys(result) AS key1
     , jsonb_array_elements(result->key1->0->'level2') AS l2_val
GROUP  BY 1
-- HAVING ?

This is assuming:

Always only one key name in the outer level of the JSON object.
Always only one array element in level1.
Key name of nested array is level2'.

I guess you want to identify those that do have messages, but all empty ...

Count objects in jsonb array with Postgres

1 Answers