Querying nested Json object in postgres

Question

I have a jsonb column in my table and data is in this format:

[
  {
    "id": 1,
    "DATA": {
      "a": "XXX",
      "key": "value1"
    }
  },
  {
    "id": 2,
    "DATA": {
      "a": "XXX",
      "key": "value2"
    }
  }
]

I would like to get the count of rows in which key = value1. I tried some queries like:

select count(t.id) 
from my_table t, 
jsonb_array_elements(summary->'DATA') elem 
where elem->>'key' = 'value1';

It returned 0 rows, though there are rows in db with that key value pair. Thanks in advance,

Answer 1

Use jsonb_array_elements() for the column summary as it is in the form of json array.

select count(distinct t.id) 
from my_table t
cross join jsonb_array_elements(summary) elem 
where elem->'DATA'->>'key' = 'value1';

Alternatively, you can get rid of the function using @> operator:

select count(t.id) 
from my_table t
where summary @> '[{"DATA":{"key":"value1"}}]'

The second solution should be faster.

Db<>fiddle.