I have field of type json. Example value is:
[{"id": "960287", "src_ip": "X.X.X.X", "filename": "XXX-20200408092811-0", "order_id": "199926", "download_ip": "", "datetime_add": "2020-04-09 09:16:48", "order_desk_id": null, "datarejestracji": null, "download_browser": "", "datetime_download": "0000-00-00 00:00:00"}, {"id": "960288", "src_ip": "2.128.4.33", "filename": "XXX-20200408101526-4", "order_id": "199926", "download_ip": "", "datetime_add": "2020-04-09 09:16:48", "order_desk_id": null, "datarejestracji": null, "download_browser": "", "datetime_download": "0000-00-00 00:00:00"}, {"id": "960751", "src_ip": "2.128.4.33", "filename": "20200409164205-24", "order_id": "199926", "download_ip": "", "datetime_add": "2020-04-09 20:02:46", "order_desk_id": null, "datarejestracji": null, "download_browser": "", "datetime_download": "0000-00-00 00:00:00"}]
How to select specified property in SQL query?
Advertisement
Answer
If you are using MySQL 5.7 or later, you can use the JSON_EXTRACT() function:
SELECT JSON_EXTRACT(myjsoncolumn, '$[0].src_ip') AS src_ip FROM mytable;
There’s also a shorthand syntax:
SELECT myjsoncolumn->'$[0].src_ip' AS src_ip FROM mytable;
Note that this function returns JSON, not the scalar string value, so it’ll look like "X.X.X.X" with quotes around it. If you want the raw value, use:
SELECT JSON_UNQUOTE(JSON_EXTRACT(myjsoncolumn, '$[0].src_ip')) AS src_ip FROM mytable;
Or the shorthand:
SELECT myjsoncolumn->>'$[0].src_ip' AS src_ip FROM mytable;
Read the documentation on JSON functions for more information: https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
If you are not using at least MySQL 5.7, you should upgrade. Older versions are now past their end-of-life, and if you’re storing JSON data in the database, it will be very difficult to do anything but return the whole JSON document to clients.
Given the structure of your example JSON, I think you might need to use JSON_TABLE().
It looks like you have an array of JSON objects with identical fields, so I wonder why you are using JSON at all, instead of storing these objects as rows with normal columns.