jsonmariadb

How to get the object key in JSON_TABLE


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

Solution

  • 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 |
    +------+---------+----------------+