Solution for Differentiate JSON object vs JSON array in postgres
is Given Below:
Let’s say I have a JSON file that is used to represent a class, however, as you see below, member could be a JSON object when there’s only 1 element, but can also be a JSON array when there’s more than one element.
{
"classes":{
"class":[
{
"name":"A",
"base":{
"name":"B"
}
},
{
"name":"C",
"base":{
"name":"B"
},
"member":{
"name":"12B",
"type":"1B9"
}
},
{
"name":"D",
"member":[
{
"name":"34B",
"type":"1B0"
},
{
"name":"6EA",
"type":"1B0"
},
{
"name":"3C38",
"type":"1B0"
},
{
"name":"4FBA",
"type":"1B0"
},
{
"name":"2F82",
"type":"bool"
}
]
}
]
}
}
This is causing some problems when I am using when I am using json_array_elements
on this JSON file, I was wondering if Postgres provide any function to check if it’s an array or object? test_class only has 1 column (info), which is the JSON data. My current solution is to write a python script to parse it, I was wondering if there’s any better solution?
SELECT x.name, x.type, class_name2.name AS class_name
FROM ICADS_JSON t, json_array_elements(t.info->'classes'->'class') as json_array
CROSS JOIN lateral json_array_elements(json_array->'member') as member
CROSS JOIN LATERAL json_to_record(member) as x(name text, type text) RIGHT JOIN
(SELECT class_name->>'name' AS name
FROM ICADS_JSON test_class, lateral json_array_elements(test_class.info->'classes'->'class') as class_name) as class_name2 ON
class_name2.name = json_array->>'name'
returns
ERROR: cannot call json_array_elements on a non-array