How to extract value by key in MySQL 8.0

Question

I have a json and i want to extract the value by a key. Not sure how I can implement it. Any suggestion is appreciated.

set @JSON = 
'[
{
    "key": "address",
    "value": "xxx"
},
{
    "key": "addr_lng",
    "value": 100.22
},
{
    "key": "addr_lat",
    "value": 300.22
}
]';

I want to extract the value by key addr_lat, but don't want change the json structure.

300.22

Consider [this](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=31dd97ec908b084f7f1f7178627865e0) — Barbaros Özhan, Dec 06 '19 at 05:19

score -2 · Answer 1 · answered Dec 06 '19 at 05:11

-2

Try this

SELECT JSON_EXTRACT('{"address": xxx, "addr_lng": 100.22, "addr_lat": 300.22}', '$.addr_lng') AS 'result';

https://database.guide/json_extract-return-data-from-a-json-document-in-mysql/

answered Dec 06 '19 at 05:11

Suraj

363
2
16

That's not an array of objects, it's a single object. – Barmar Nov 03 '21 at 16:07

How to extract value by key in MySQL 8.0

1 Answers1