2

I have a complex nested json structure in a postgres json field. I want to list all element values with key '$type' no matter where in the nested structure they appear. The structure contains arrays nested within arrays to several levels deep. What is the sql query I should use?

The table structure is:

create table if not exists documents
(
  id text not null
    constraint documents_pkey primary key,
  value json not null
)
Kobus Herbst
  • 415
  • 2
  • 12

2 Answers2

1

This recursive function extracts all attributes from a complex jsonb object:

create or replace function jsonb_extract_all(jsonb_data jsonb, curr_path text[] default '{}')
returns table(path text[], value text)
language plpgsql as $$
begin
    if jsonb_typeof(jsonb_data) = 'object' then
        return query 
            select (jsonb_extract_all(val, curr_path || key)).*
            from jsonb_each(jsonb_data) e(key, val);
    elseif jsonb_typeof(jsonb_data) = 'array' then
        return query 
            select (jsonb_extract_all(val, curr_path || ord::text)).*
            from jsonb_array_elements(jsonb_data) with ordinality e(val, ord);
    else
        return query
            select curr_path, jsonb_data::text;
    end if;
end $$;

Example usage:

with my_table(data) as (
select
    '{
        "$type": "a",
        "other": "x",
        "nested_object": {"$type": "b"},
        "array_1": [{"other": "y"}, {"$type": "c"}],
        "array_2": [{"$type": "d"}, {"other": "z"}]
    }'::jsonb
)

select f.*
from my_table
cross join jsonb_extract_all(data) f
where path[cardinality(path)] = '$type';

         path          | value 
-----------------------+-------
 {$type}               | "a"
 {array_1,2,$type}     | "c"
 {array_2,1,$type}     | "d"
 {nested_object,$type} | "b"
(4 rows)
klin
  • 112,967
  • 15
  • 204
  • 232
  • Thanks you, as per your example it accomplishes what I am trying to do. I ran it on a real world json structure with thousands (+- 25,000) of elements and I needed to terminate the query after a couple of hours as it didn't complete. I am sure the json structure is well-formed as I could parse it with json.net. Haven't had time to delve deeper into this. – Kobus Herbst Mar 02 '18 at 07:58
  • Searching for attributes in a large json document with an unknown structure is not a job for a relational database. Generally, you have two options, transpose data into the form of relational tables or try to use one of the no-sql solutions like [Couchbase Server.](https://www.couchbase.com/) – klin Mar 02 '18 at 11:32
0

You can use a resursive query. I have done most of the work here:

with recursive dived(jkey, jval, jtype) as (
 select t.key, t.value, 
  json_typeof(t.value) jtype
  from  json_each('{"id":"243769","name":"domains","type":"TABLE","adata":{"sfield":"name"},"fields":{"id":{"ind":1,"enum":null,"refs":[null,null],"reqd":true,"type":"int4","constr":["p",null],"default":null},"name":{"ind":2,"enum":null,"refs":[null,null],"reqd":true,"type":"text","constr":["u",null],"default":null},"appid":{"ind":5,"enum":null,"refs":["apps","id"],"reqd":true,"type":"int4","constr":[null,null],"default":null},"userid":{"ind":8,"enum":null,"refs":["users","id"],"reqd":true,"type":"int8","constr":[null,null],"default":null},"createdat":{"ind":6,"enum":null,"refs":[null,null],"reqd":true,"type":"timestamptz","constr":[null,null],"default":null},"updatedat":{"ind":7,"enum":null,"refs":[null,null],"reqd":true,"type":"timestamptz","constr":[null,null],"default":null},"subdomainforward":{"ind":4,"enum":null,"refs":[null,null],"reqd":false,"type":"text","constr":[null,null],"default":null},"wilcardsubdomain":{"ind":3,"enum":null,"refs":[null,null],"reqd":false,"type":"bool","constr":[null,null],"default":null}},"schema":"web","relchecks":0,"relhasrules":false,"relhastriggers":true,"relrowsecurity":false,"relforcerowsecurity":false}'::json) t

   union all

  select t.key, t.value,
    json_typeof(t.value) jtype
    from dived, json_each(dived.jval) as t
      where dived.jtype in ('object' /*, 'array'*/)
 )

select * From dived where jkey = 'yourkey' limit 100

You will simply need to add in an case when or some logic when it comes to arrays and json_array_elements.

Iterating through nested arrays with json is not too difficult with a recursive query but I find it tedious.

Place the CASE WHEN in front of the json_each as something like:

  CASE WHEN dived.jtype = 'array' then
  json_array_elements(dived.jval) t

It may be possible to handle the situation with the case when scenario, otherwise you may need a separate recursive query specifically for arrays and then do a union with the object keys/values.

You also may find more info here: Collect Recursive JSON Keys In Postgres

I hope this helps!

Martin Jstone
  • 31
  • 4