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!
