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

Pick keys from objects in array in MySQL JSON column

I have a table with a column for JSON.

Example of the JSON in the column:

“[{ “plys”: 3, “end”: 47.56, “length”: 47.56 }, { “plys”: 2, “end”: 62.97, “length”: 15.41 }]”

There are other fields in each object that are not in every object but not showing those for simplicity.

I would like to get the information in the following format. Still just a single column but objects concatenated and comma delineated for each array element.

3 – 47.56, 2 – 15.41

Could even get it in json format with something like:

[[3, 47.56], [2, 15.41]]

Or

[{ “plys”: 3, “length”: 47.56 }, { “plys”: 2, “length”: 15.41 }]

I am giving 3 different outputs because I don’t know what is possible and also practical.

We have a working solution but not great because the separation isn’t clear using

JSON_EXTRACT(our_column, ‘$[].plys’, ‘$[].length’)

and it puts out an array like below

[ 3, 47.56, 2, 15.41 ]

As for database design, yes this should be a separate table. This was done in a rush to make it easier for querying since the front end uses the array for display and we are not using the data for any queries. This is for a simple report export to limit information. First time using a JSON column and thought this would be useful case but is not as easy to work with as we originally thought.

Tags:

2

Answers


  2. 0 
    SELECT JSON_ARRAYAGG(
  JSON_ARRAY(j.plys, j.length)
) AS _result
FROM mytable
CROSS JOIN JSON_TABLE(data, '$[*]' COLUMNS (
    plys INT PATH '$.plys',
    `end` NUMERIC(9,2) PATH '$.end',
    length NUMERIC (9,2) PATH '$.length'
  )) AS j
GROUP BY mytable.id;

    Result:

    +--------------------------+
| _result                  |
+--------------------------+
| [[3, 47.56], [2, 15.41]] |
+--------------------------+

    Demo: https://dbfiddle.uk/esRcH3-h

    Using JSON in a relational database frequently makes queries harder, not easier.

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

Back To Top
Search