CODEWITHSUNDEEP
mysqlsqlmysql-json
OArmas
OArmas

Reputation: 1

Extract sub JSON with keys and values in MYSQL

I have a table with a JSON TYPE column like this:

id json_info date
1. {"Ccclaptop_model": {"value": "asus", "price": "2122"}, "laptop_ram": {"value": "6", "price": "122"}, "laptop_mouse_model": {"value": "logic", "price": "30"} , "laptop_keys_model": {"value": "USA", "price": "10"}} 2020-02-02

And I'd like to have a query that extracts a sub-JSON with his key value, like this:

id json_info date
1. {"laptop_mouse_model": {"value": "logic", "price": "30"} , "laptop_keys_model": {"value": "USA", "price": "10"}} 2020-02-02

I have tried JSON_EXTRACT

    SELECT id,
       JSON_EXTRACT(json_info, '$.laptop_mouse_model', '$.laptop_keys_model' ) AS store_id 
FROM feature_data WHERE date = '2020-02-02';

but just return a JSON array without keys, like this:

id json_info date
1. [{"value": "logic", "price": "30"} ,{"value": "USA", "price": "10"}] 2020-02-02

Does somebody know if exists a function that I could use?

Upvotes: 0

Views: 324

Answers (1)

Cositanto
Cositanto

Reputation: 750

You can use JSON_OBJECT to combine result to a json, for example:

SELECT id,JSON_OBJECT('laptop_mouse_model',JSON_EXTRACT(json_info, '$.laptop_mouse_model'),'laptop_keys_model',JSON_EXTRACT(json_info, '$.laptop_keys_model')) AS store_id FROM feature_data WHERE date = '2020-02-02';

Upvotes: 1

Related Questions