Home > mailing lists

help flattening json - Mailing list pgsql-general

From	Chris Stephens
Subject	help flattening json
Date	September 27, 2020 23:54:55
Msg-id	CAEFL0sx9DMr4Z35SCPxr8=6s1sVB1cPFvQ8WqM60nAnTZ_dhrQ@mail.gmail.com Whole thread Raw
List	pgsql-general

Tree view

posgresql verion: 12

i can accomplish this procedurally fairly easily but would like to do this strictly in SQL.

jsondb=# select jsonb_typeof(jsonb_path_query(vdl_json,'$.tables[*]')) from vdl_json2;
jsonb_typeof
--------------
object
object
object
object
object
object
(6 rows)

jsondb=# select jsonb_pretty(jsonb_path_query(vdl_json,'$.tables[1]')) from vdl_json2;
jsonb_pretty
--------------------------------------------
{ +
"name": "AMP_DATA", +
"quoted": true, +
"columns": [ +
{ +
"field": "WELL", +
"selected": true, +
"displayName": "Well" +
}, +
{ +
"field": "WELL_POSITION", +
"selected": true, +
"displayName": "Well Position"+
}, +
{ +
"field": "CYCLE_NUMBER", +
"selected": true, +
"displayName": "Cycle Number" +
}, +
{ +
"field": "TARGET", +
"selected": true, +
"displayName": "Target" +
}, +
{ +
"field": "RN", +
"selected": true, +
"displayName": "Rn" +
}, +
{ +
"field": "DRN", +
"selected": true, +
"displayName": "dRn" +
}, +
{ +
"field": "SAMPLE", +
"selected": true, +
"displayName": "Sample" +
}, +
{ +
"field": "OMIT", +
"selected": true, +
"displayName": "Omit" +
} +
], +
"labeled": false, +
"options": { +
}, +
"displayName": "Amplification Data", +
"sortedColumns": [ +
], +
"analysisModule": "primary" +
}
(1 row)

i would like to end up with (name text, field text[]).

I can get the field array when i manually filter on name:

jsondb=# select jsonb_path_query_array(vdl_json,'$.tables ? (@.name == "RAW_DATA").columns.field') fields
from vdl_json2;
fields
-------------------------------------------
["WELL", "WELL_POSITION", "CYCLE_NUMBER"]
(1 row)

I can get the text of names:

jsondb=# \e
name
--------------------------
"RESULT"
"AMP_DATA"
"MULTICOMPONENT"
"RAW_DATA"
"REPLICATE_GROUP_RESULT"
"WELL_CALL"
(6 rows)

I think i should be able to do this with a lateral join but i can't seem to get it right. something like:

jsondb=# select tnames.tname, tfields_arr.* from
(select jsonb_path_query(vdl_json,'$.tables[*].name') as tname
from vdl_json2) tnames,
lateral
(select jsonb_path_query_array(vdl_json,'$.tables ? (@.name == tnames.tname).columns.field') as tfields
from vdl_json2) tfields_arr;
ERROR: syntax error at or near " "
LINE 6: from vdl_json2) tfields_arr;

Any json folks out there willing to help out?

i'd also like to get records of "(name text, field text, selected text, displayName text)" but i haven't started on that one yet.

any help is greatly appreciated!

pgsql-general by date:

From: Adrian Klaver
Date: 27 September 2020, 20:32:00
Subject: Re: pg_upgrade Python version issue on openSUSE

From: "aNullValue (Drew Stemen)"
Date: 28 September 2020, 00:16:54
Subject: Returning timestamp with timezone at specified timezone irrespective of client timezone

help flattening json - Mailing list pgsql-general

Previous

Next