I'm trying to pull a value out of nested JSON inside a column. I get the return value, but I am unable to also pull the first JSON key that the value binds to.

Query only pulls 1 row.

Example json:

{
    "key1": {
        "data1": "data1 object",
        "data2": "data2 object"
    },
    "key2": {
        "data1": "data1 object",
        "data2": "data2 object"
    }
}

where key key values are dynamic and key data values are static.

Expected result:

{
    "key1": {
        "data1": "data1 object"
    },
    "key2": {
        "data1": "data1 object"
    }
}

or

{
    "key1": "data1 object",
    "key2": "data1 object"
}

Not perfectly working query:

SELECT
    json->>"$.*.data1" AS data
FROM table
WHERE id=1;

output:

[
    "data1 object",
    "data1 object"
]

CodePudding user response：

SELECT test.id, JSON_OBJECTAGG(jsonkeys.onekey, jsonvalues.onevalue) output
FROM test
CROSS JOIN JSON_TABLE(JSON_KEYS(test.jsondata),
                      '$[*]' COLUMNS (onekey VARCHAR(255) PATH '$')) jsonkeys
CROSS JOIN JSON_TABLE(JSON_EXTRACT(test.jsondata, CONCAT('$.', jsonkeys.onekey)),
                      '$.data1' COLUMNS (onevalue VARCHAR(255) PATH '$'))jsonvalues
GROUP BY test.id

https://dbfiddle.uk/uNpzif3g

CodePudding user response：

Was able to resolve it with using JSON_KEYS(), effectively returning a map. I have checked and tested if indexes always correlate and they do.

Updated query:

SELECT
    JSON_KEYS(json) AS map,
    json->>"$.*.data1" AS data
FROM table
WHERE id=1;

Result: