How to extract JSON object from array?

Question

MySQL

I have a table with two columns id(int) and users(json). The users column stores users as an array of JSON objects:

[{"user_id": 52 , "name": "Tomas"}, {"user_id": 59 , "name": "John"}, {"user_id": 79 , "name": "Alex"}]

How to compose a query to, for example, get the username whose user_id = 59 ?

Answer 1

I just thought of this

select * from t;
+-------------------------------------------------------------------------+
| u                                                                       |
+-------------------------------------------------------------------------+
| [{"name": "Tomas", "user_id": "52"}, {"name": "John", "user_id": "59"}] |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_EXTRACT(u, REPLACE(REPLACE(JSON_SEARCH(u, 'one', '52'), 'user_id', 'name'), '"', '')) FROM t;
+--------------------------------------------------------------------------------------------+
| JSON_EXTRACT(u, REPLACE(REPLACE(JSON_SEARCH(u, 'one', '52'), 'user_id', 'name'), '"', '')) |
+--------------------------------------------------------------------------------------------+
| "Tomas"                                                                                    |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)