The tables in the query are not designed or controlled by me. Please don’t tell me to change the table structure because it is outside my scope.
using
"PostgreSQL 11.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit"
This is a snippet of the table structure. All json is stored as character varying. This is one column in the table. For this exercise, just call the table "ThisTable" and the column "Brands".
[
{
"site": "https://brand.map.com/cur",
"type": {
"coding": [
{
"code": "gee",
"display": "Green Field",
"site": "http://ag.org/clear/latest/green-fld/Coding/GrType",
"version": "1.0.0"
}
]
},
"value": "101B_xx20048TS5M_113"
},
{
"site": "https://brand.map.com/cur",
"type": {
"coding": [
{
"code": "gee",
"display": "Green Field",
"site": "http://ag.org/clear/latest/green-fld/Coding/GrType",
"version": "1.0.0"
}
]
},
"value": "50499_PR2051TGY_211"
},
{
"site": "https://brand.map.com/cur",
"type": {
"coding": [
{
"code": "gee",
"display": "Green Field",
"site": "http://ag.org/clear/latest/green-fld/Coding/GrType",
"version": "1.0.0"
}
]
},
"value": "931002492_GHJ120044VDA22_321"
},
{
"site": "https://brand.map.com/cur",
"type": {
"coding": [
{
"code": "gee",
"display": "Green Field",
"site": "http://ag.org/clear/latest/green-fld/Coding/GrType",
"version": "1.0.0"
}
]
},
"value": "4023451_JK49880RWD_521"
},
{
"site": "https://brand.map.com/cur",
"type": {
"coding": [
{
"code": "gee",
"display": "Green Field",
"site": "http://ag.org/clear/latest/green-fld/Coding/GrType",
"version": "1.0.0"
}
]
},
"value": "71093129_PR3225TGY_211"
},
{
"site": "https://brand.map.com/cur",
"type": {
"coding": [
{
"code": "gee",
"display": "Green Field",
"site": "http://ag.org/clear/latest/green-fld/Coding/GrType",
"version": "1.0.0"
}
]
},
"value": "71093419_PR3225TGY_211"
}
]
the goal is to find a particular site (upper level in the nesting- example "https://brand.map.com/cur")
for a particular code and system in the nested type/coding structure. (example is code "gee" for site "http://ag.org/clear/latest/green-fld/Coding/GrType" for the Brands column.
this could be used in either a select or a where clause (e.g. need to display it and/or use it as selection criteria)
I can break out the Brands column and get the type and/or coding, but I am failing to see how to break out the coding array. I’ve been using a cross join. This shows a way to break out the details but fails if I try to add the site type array.
from ThisTable TT
cross join jsonb_array_elements(TT.Brands::jsonb) as ttb(site_arr)
--cross join jsonb_array_elements(ttb.site_arr->>'type') as ttbs(sitetype_arr)
I can work around this but mostly want to understand. This is the selection I need to make (I think) but I can’t select type or coding to extract this and then cross join.
I’m not sure if unnest is a better option. I could not get json_path_query to work in this case.
SELECT brandtype->>'code' as brand_type_code
FROM jsonb_array_elements('[
{
"code": "gee",
"display": "Green Field",
"site": "http://ag.org/clear/latest/green-fld/Coding/GrType",
"version": "1.0.0"
}
]') as brandtype
2
Answers
Maybe this will helped you. If you need anything else, let me, I will explain or help.
If I recapitulate what you are doing by filling in the missing pieces and then uncommenting the commented out jsonb_array_elements I get this error:
(But you should show the error yourself, not make us re-discover it.) And the reason for this error is that
->>yields text, not jsonb. If I switch it to->to preserve the type as jsonb, then I get a different error:Which is because
->'type'on your data yields an object, not an array. You can pull the array out of that object by adding one more level of referencing, so:Now this does work. It yields the same thing 6 times, but that is because your data just has the same thing repeated 6 times; there is nothing we can do about your data being silly.