Question 113108 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Extract second level object from JSON while preserving first level key in return value

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"
]
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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:

    map data
    ["key1", "key2"] ["data1 object", "data1 object"]

  2. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search