Aggregate query on a Postgres jsonb array based on a condition

Question

I have a postgres table (documents) which has a jsonb column called details which holds a jsonb that looks like this:

{
  "identifier": "lead_id",
  "callback_url": "http://localhost:3000/supply/internal/api/v3/attempts/9a967788-ad44-499e-93e8-3ce6544f3786",
  "document_ids": [
    "0246ef40-db40-4c07-898c-7c09ad50d3ff",
    null,
    "c155d537-2faf-4397-90ba-741651fdee9d",
    "f926ffc5-6184-44ad-ac16-3da7c0bc3186",
    "68f91648-9297-4f34-b320-d00cec04f52c",
    "2159baf8-55a9-40f9-a998-3b237f370b3a"
  ],
  "identifier_value": "9a967788-ad44-499e-93e8-3ce6544f3786"
}

document_ids is a jsonb array which holds a list of document ids some of which may be null. I want to run an aggregation query such that in output I get a list of distinct identifier_value and the number of document_ids which are null.

For instance, for the above json, the output should be: 9a967788-ad44-499e-93e8-3ce6544f3786, 1.

Cant think of how such a query would look like. Any help is appreciated!

klin · Accepted Answer · 2019-10-11 07:08:23Z

1

You can use the function jsonb_array_elements():

select details->'identifier_value', sum((value = 'null'::jsonb)::int)
from documents
cross join jsonb_array_elements(details->'document_ids')
group by 1
order by 2 desc

Db<>fiddle.

In Postgres 12 use the jsonpath type:

select details->'identifier_value', count(d)
from documents
left join jsonb_path_query(details, '$.document_ids[*] ? (@ == null)') as d on true
group by 1
order by 2 desc

Db<>fiddle.

Read in the documentation:

edited Oct 11, 2019 at 7:08

answered Oct 10, 2019 at 10:41

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user248884 Over a year ago

Works well! Just one question, how do we order by the sum in the descending order? I tried to alias the sum value and then do an order by - that doesnt work.

klin Over a year ago

Use the number of the column, see the updated answer.

Collectives™ on Stack Overflow

Aggregate query on a Postgres jsonb array based on a condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related