SQL

how to get json data mysql

SELECT 
    id, client_id, service_id,
    json_extract(service_values, '$.quota') AS quota,
    json_extract(service_values, '$.free_quota') AS free_quota,
    json_extract(service_values, '$.total_accounts') AS total_accounts,
    json_extract(service_values, '$.data_transfer') AS data_transfer
FROM client_services;

mysql json query

+----+--------+-------+-------+---------------------------------+
| id | name   | price | stock | attr                            |
+----+--------+-------+-------+---------------------------------+
|  2 | Shirt  | 10.50 |    78 | {"size": 42, "colour": "white"} |
|  3 | Blouse | 17.00 |    15 | {"colour": "white"}             |
+----+--------+-------+-------+---------------------------------+
  
SELECT name FROM clothes WHERE JSON_EXTRACT(attr, '$.size') = 42;

mysql json

SELECT json_clmn->'$.property' FROM table

how to get json data mysql

SELECT 
    ID, CITY,
    json_extract(POPULATION_JSON_DATA, '$.male') AS POPL_MALE,
    json_extract(POPULATION_JSON_DATA, '$.female') AS POPL_FEMALE,
    json_extract(POPULATION_JSON_DATA, '$.other') AS POPL_OTHER
FROM TBL_JSON;

MYSQL JSON OBJECT

SELECT 
o_id, 
CONCAT(
    '{', 
    GROUP_CONCAT(
        CONCAT(
            '"',
            `attribute`,
            '":"',
            `value`,
            '"'
            )
        ),
    '}'
    ) json 
FROM t3 
GROUP BY o_id