Jsonb object parsing in PostgreSql

Question

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.

If there is no fixed structure how should you be able to parse the json object in a classical way? In you case the only chance you have is to cast it into a text value and do a LIKE comparison: `json_value::text LIKE '"attribute_id":"363698007"'` — S-Man, May 15 '19 at 13:58

score 0 · Accepted Answer · answered May 15 '19 at 14:44

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.

Jsonb object parsing in PostgreSql

1 Answers1