MYSQL JSON how i can get all name and value from table attributes

Question

{
    "1559a633-9037-11ed-a63f-000c292e0bd3": {
        "name": "speed",
        "value": "230"
    },
    "23d1e822-90c1-11ed-a63f-000c292e0bd3": {
        "name": "power",
        "value": "12v"
    },
}

i try

SELECT attributes->'$.name' AS name FROM products;

and get null result

I wont too see first example unique name and second example name and value

If try SELECT attributes->'$.name' AS name FROM products; im get NULL — user367970, Jan 12 '23 at 09:26
I have explained you why you receive NULL already. There is no `$.name`, there are `$."1559a633-9037-11ed-a63f-000c292e0bd3".name` and `$."23d1e822-90c1-11ed-a63f-000c292e0bd3".name` in your JSON. — Akina, Jan 12 '23 at 09:29
thank you, but how can all $.name this line $."1559a633-9037-11ed-a63f-000c292e0bd3" unique — user367970, Jan 12 '23 at 09:55
Study [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055). Provide sample data (#5) and desired output for it (#3). Specify precise MySQL version (output for `SELECT @@version;`). — Akina, Jan 12 '23 at 10:02

score 0 · Answer 1 · answered Jan 12 '23 at 14:11

Here's a solution to convert the name, value pairs back to columns as if you had stored the data in a normal manner:

SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(mytable.attributes, CONCAT('$."', k.id, '".name'))) AS name,
  JSON_UNQUOTE(JSON_EXTRACT(mytable.attributes, CONCAT('$."', k.id, '".value'))) AS value
FROM mytable
CROSS JOIN JSON_TABLE(JSON_KEYS(mytable.attributes), '$[*]' COLUMNS (id CHAR(36) PATH '$')) AS k;

Output:

+-------+-------+
| name  | value |
+-------+-------+
| speed | 230   |
| power | 12v   |
+-------+-------+

From there I'll trust you can figure out how to put that in a derived table subquery and do the operations you need to do on it.

Note that using JSON_TABLE() requires MySQL 8.0. If you use an older version of MySQL, it won't work.

This begs the question of why didn't you just store the data in the normal way to start with. In my opinion, using JSON in MySQL makes a lot of queries more complex and difficult to write, difficult to debug, difficult to optimize. The queries would be simpler if you stored this data as multiple rows of another table, instead of using JSON.

MYSQL JSON how i can get all name and value from table attributes

1 Answers1