How to parse jsonb object in PostgreSql. The problem is - object every time is different by structure inside. Just like below.
{
"1":{
"1":{
"level":2,
"nodeType":2,
"id":2,
"parentNode":1,
"attribute_id":363698007,
"attribute_text":"Finding site",
"concept_id":386108004,
"description_text":"Heart tissue",
"hierarchy_id":0,
"description_id":-1,
"deeperCnt":0,
"default":false
},
"level":1,
"nodeType":1,
"id":1,
"parentNode":0,
"concept_id":22253000,
"description_id":37361011,
"description_text":"Pain",
"hierarchy_id":404684003,
"deeperCnt":1,
"default":false
},
"2":{
"1":{
"attribute_id":"363698007",
"attribute_text":"Finding site (attribute)",
"value_id":"321667001",
"value_text":"Respiratory tract structure (body structure)",
"default":true
},
"level":1,
"nodeType":1,
"id":3,
"parentNode":0,
"concept_id":11833005,
"description_id":20419011,
"description_text":"Dry cough",
"hierarchy_id":404684003,
"deeperCnt":1,
"default":false
},
"level":0,
"recAddedLevel":1,
"recAddedId":3,
"nodeType":0,
"multiple":false,
"currNodeId":3,
"id":0,
"lookForAttributes":false,
"deeperCnt":2,
}
So how should I parse all object and for example look if object inside has "attribute_id" = 363698007? In this case we should get 'true' while selecting data rows in PostgreSql with WHERE statement.
2 question - what index should I use for jsonb column to get wanted results? Already tried to create btree and gin indexes but even simple select returns 'null' with sql like this:
SELECT object::jsonb -> 'id' AS id
FROM table;
if I use this:
SELECT object
FROM table;
returns firstly described object.
The quick and dirty way (extended upon Collect Recursive JSON Keys In Postgres):
WITH RECURSIVE doc_key_and_value_recursive(id, key, value) AS (
SELECT
my_json.id,
t.key,
t.value
FROM my_json, jsonb_each(my_json.data) AS t
UNION ALL
SELECT
doc_key_and_value_recursive.id,
t.key,
t.value
FROM doc_key_and_value_recursive,
jsonb_each(CASE
WHEN jsonb_typeof(doc_key_and_value_recursive.value) <> 'object' THEN '{}'::jsonb
ELSE doc_key_and_value_recursive.value
END) AS t
)
SELECT t.id, t.data->'id' AS id
FROM doc_key_and_value_recursive AS c
INNER JOIN my_json AS t ON (t.id = c.id)
WHERE
jsonb_typeof(c.value) <> 'object'
AND c.key = 'attribute_id'
AND c.value = '363698007'::jsonb;
Online example: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=57b7c4e817b2dd6580bbf28cbac10981
This may be improved a lot by stopping the recursion as soon as the relevant key and value are found, reverse sort and limit 1, aso. But it does the basic thing generically.
It also shows that jsonb->'id'
does work as expected.