Extract Key-Value pairs by conditionally looping over a JSONB array of records in Postgressql

Question

I want to write a query that can loop over a list of dictionaries in jsonb object and extract all values that match a specific condition. The table has the following structure.

UniqueID	Configuration
uid1	[{...}, {...}, {...}]
uid2	[{...}, {...}, {...}]

The list of dictionaries looks like following for each uid.

[
  {'insType': 1, 'insMode': 2, 'insOffset': 0.0},
  {'insType': 2, 'insMode': 4, 'insOffset': 1.0},
  {'insType': 2, 'insMode': 5, 'insOffset': 0.0},
  {'insType': 3, 'insMode': 2, 'insOffset': 3.0},
  {'insType': 3, 'insMode': 0, 'insOffset': -1.0},
]

I want to find the 'insOffset' for 'insType' == 2 for all my unique ids.

Is there even a way to do it as a query?

score 1 · Accepted Answer · answered Jul 29 '23 at 03:06

This is most easily done by using jsonb_to_recordset from the JSON processing functions in a lateral join

SELECT e."uniqueId", c."insOffset"
FROM example e, jsonb_to_recordset(e."Configuration") AS c("insType" int, "insOffset" float)
WHERE c."insType" = 2;

Otherwise, you can use jsonb_array_elements and access the individual properties, converting them to floats and to integers respectively:

SELECT e."uniqueId", (config ->> 'insOffset')::float AS "insOffset"
FROM example e, jsonb_array_elements(e."Configuration") AS config
WHERE (config ->> 'insType')::int = 2;

Extract Key-Value pairs by conditionally looping over a JSONB array of records in Postgressql

1 Answers1