I need parse JSON to Greenplum

Question

How to parse JSON

{
"35673": [
        "234",
        "357",
        "123"
    ],
    "34566": [
        "333",
        "456",
        "789"
    ]
}

to Greenplum table, format 1 key 1 value?

Answer 1

In your basic example, there is no json nested object, no array of json object, and all the object values are arrays of string. So within these assumptions, you can use the json_path_query function with the jsonpath operator keyvalue() so that to split your json object into a set of key/value and then use the json_array_elements function so that to split the resulting values which are systematically 1-Dim arrays :

SELECT j->>'key' AS Key, json_array_elements(j->'value')->>0 AS Value
FROM json_path_query (your_json_object_here, '$.keyvalue()') AS j

The output for your example is :

Key     Value     
34566   333
34566   456
34566   789
35673   234
35673   357
35673   123

All these functions can apply to the jsonb type which is preferred to the json type, see the manual :

"The json and jsonb data types accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage."

If your json structure is more complex with nested objects or arrays of objects, you will have to adapt the function in the FROM clause. If your object values are not systematically arrays of string, you will have to adapt the function applied to the resulting json value field.