In a multi-dimensional JSON object (a map with a map) I want to extract the keys EN
and ES
(the language) together with the properties. How is that possible?
JSON:
{
"EN": {
"name": "name en",
"description": "description en"
},
"ES": {
"name": "name es",
"description": "description es"
}
}
This will fetch each map with the translation, but I also want to retrieve the parent key for the map (EN
and ES
)
SQL
SELECT jt.name, jt.description
FROM
payment_term p,
JSON_TABLE(
p.document_languages, '$.*' COLUMNS (
name TEXT PATH '$.name'
description TEXT PATH '$.description'
)
) as jt
WHERE p.id=31
This will output
name | description
------------------------------
name en | description en
name es | description es
But I want something like
lang | name | description
------------------------------
en | name en | description en
es | name es | description es
You can use JSON_KEY_VALUE
to get the key:
SELECT jt.* FROM JSON_TABLE(
JSON_KEY_VALUE('{ "EN": { "name": "name en", "description": "description en" }, "ES": { "name": "name es", "description": "description es" }}', '$'), '$[*]'
COLUMNS (
lang TEXT PATH '$.key',
name TEXT PATH '$.value.name',
description TEXT PATH '$.value.description' )) AS jt;
Output:
+------+---------+----------------+
| lang | name | description |
+------+---------+----------------+
| EN | name en | description en |
| ES | name es | description es |
+------+---------+----------------+