CODEWITHSUNDEEP
mysqljson
Suresh
Suresh

Reputation: 1169

How to fetch particular value from JSON Array MySQL

 Set @a='[
      {
        "lng": "ch",    
         "zone_name": "簡體中文",
         "zone_location": "A區位置",
         "about_zone": "區域描述在這裡輸入"     
      },
      {
        "lng": "eng",    
         "zone_name": "Zone B ENG",
         "zone_location": "Zone B Location ENG",
         "about_zone": "About Zone EN"     
      },
      {
        "lng": "jp",    
         "zone_name": "ゾーン名はこちら",
         "zone_location": "ゾーンの場所はこちら",
         "about_zone": "ゾーンの説明はここに入力してください"     
      },
      {
        "lng": "es",    
         "zone_name": "Zone Locatio aquí",
         "zone_location": "Nombre de ubicación aquí",
         "about_zone": "La descripción debe ingresar aquí sobre nosotros O puede asegurarse de que no haya texto de frenado"     
      }
    ]'

I am storing language data in JSON format, trying to fetch name based on user's language i.e if user's preferred language is English then should return English text.

SELECT JSON_EXTRACT (JSON_EXTRACT(@a,'$[3]'),'$.zone_name') ;

any suggest how to fetch data based on "lng" key fyi, will be using in Select query.

Upvotes: 0

Views: 50

Answers (1)

Nick
Nick

Reputation: 147266

You can use JSON_SEARCH to find a path to the users desired language e.g.

SELECT JSON_SEARCH(@a, 'one', 'eng', null, '$[*].lng')

This will return something like "$[1].lng". You can then JSON_UNQUOTE that value and use SUBSTRING_INDEX to get the part to the left of the ., i.e. $[1]. This can then be used as a path to JSON_EXTRACT when concatenated with .zone_name:

SELECT JSON_EXTRACT(@a, CONCAT(SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(@a, 'one', 'eng', null, '$[*].lng')), '.', 1), '.zone_name'))

Output:

"Zone B ENG"

Demo on dbfiddle

Note the output value has double quotes around it, you may want to remove those with JSON_UNQUOTE to just get Zone B ENG.

Note also that you could use REPLACE to replace .lng in the returned path from JSON_SEARCH with .zone_name:

SELECT JSON_EXTRACT(@a, REPLACE(JSON_UNQUOTE(JSON_SEARCH(@a, 'one', 'eng', null, '$[*].lng')), '.lng', '.zone_name'))

Demo on dbfiddle

If you're using MySQL 8.0+, you can use the JSON_TABLE function to make life much easier:

SELECT *
FROM JSON_TABLE(@a,
                "$[*]" COLUMNS(
                  lng VARCHAR(4) PATH "$.lng",
                  zone_name VARCHAR(20) PATH "$.zone_name",
                  zone_location VARCHAR(20) PATH "$.zone_location",
                  about_zone VARCHAR(20) PATH "$.about_zone"
                  )
                ) AS j
WHERE lng = 'eng'

Output:

lng     zone_name   zone_location           about_zone
eng     Zone B ENG  Zone B Location ENG     About Zone EN

Demo on dbfiddle

Upvotes: 1

Related Questions