CODEWITHSUNDEEP
mysqlnestedmysql-json
Nilu Singh
Nilu Singh

Reputation: 1

How to retrieve specific field from nested json field in mysql?

I have following column(descrition field) in my table of data in which I have to extract country name field value. Like Spain from this field.

[
  {
    "key": "country_code",
    "type": "string",
    "value": "12"
  },
  {
    "key": "country_name",
    "type": "string",
    "value": "Spain"
  },
  {
    "key": "timezone",
    "type": "string",
    "value": "Europe/Madrid"
  },
  {
    "key": "latitude",
    "type": "string",
    "value": "40.37603"
  },
  {
    "key": "longitude",
    "type": "string",
    "value": "-3.69901"
  }
]

enter image description here

How to extract it? please help me out.

SELECT
  json_extract(description, '$.key[1]') AS country
FROM
  test_table;

Upvotes: 0

Views: 43

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

did you mean something like that

SET @j = '[
  {
    "key": "country_code",
    "type": "string",
    "value": "12"
  },
  {
    "key": "country_name",
    "type": "string",
    "value": "Spain"
  },
  {
    "key": "timezone",
    "type": "string",
    "value": "Europe/Madrid"
  },
  {
    "key": "latitude",
    "type": "string",
    "value": "40.37603"
  },
  {
    "key": "longitude",
    "type": "string",
    "value": "-3.69901"
  }
]';

SELECT JSON_EXTRACT(json_elem, '$.value') AS country_name_value
FROM JSON_TABLE(@j, '$[*]' COLUMNS (
  json_elem JSON PATH '$'
) ) AS jt
WHERE JSON_EXTRACT(json_elem, '$.key') = 'country_name';

Sample here : https://dbfiddle.uk/AgVBNJSd

Upvotes: 0

Related Questions