How to fetch particular value from JSON Array MySQL

Question

 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.

I'm not certain that the MySQL JSON functions can do what you need here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 23, 2020 at 6:34

Nick · Accepted Answer · 2020-02-23 07:03:12Z

1

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

edited Feb 23, 2020 at 7:03

answered Feb 23, 2020 at 6:36

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nick Over a year ago

@Suresh btw which version of MySQL are you using?

Suresh Over a year ago

MySQL version is 8.0.11, do you foresee any performance issue for large data? assume my table might have 100K rows in future.

Nick Over a year ago

@Suresh sorry - should have asked earlier. Please see my edit with a better solution using the MySQL 8+ JSON_TABLE function.

Suresh Over a year ago

i having some other fields also in select, will JSON_TABLE table still best choice? i.e. select zone_id,zone_name, lng_field1,lng_field2 from my_table ?

Nick Over a year ago

@Suresh if you mean outside the JSON, then it it's still good, you just need to JOIN the table to the JSON_TABLE See for example this answer

Collectives™ on Stack Overflow

How to fetch particular value from JSON Array MySQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related