How To extract Value from Json multi dimension Array In MySQL

Question

column contain value given below.


 [
  {
    "bActive": false,
    "sSubLocation": "",
    "aiSeries": [],
    "iUser": "1"
  },
  {
    "bActive": true,
    "sSubLocation": "Mytestcase",
    "aiSeries": [],
    "iUser": "1"
  }
]

I want to get result as sSubLocation key where it have bActive =true and sSubLocation = "Mytestcase";

Don't use JSON to store data you need to query on, particularly where you have multi-dimensional arrays. Create proper tables for the data. The query for this would be trivial if you'd set up your tables properly. — Tangentially Perpendicular
– Tangentially Perpendicular, Commented May 19, 2021 at 3:30
Parse (use JSON_TABLE function) then query to separate values. — Akina
– Akina, Commented May 19, 2021 at 5:25
* i want to get result as sSubLocation key where it have bActive =true and sSubLocation = "Mytestcase";* ??? — Akina
– Akina, Commented May 19, 2021 at 5:30

Akina · Accepted Answer · 2021-05-19 05:30:12Z

1

SELECT test.id, jsontable.*
FROM test
CROSS JOIN JSON_TABLE(test.value,
                      '$[*]' COLUMNS (bActive BOOLEAN PATH '$.bActive',
                                      sSubLocation VARCHAR(255) PATH '$.sSubLocation',
                                      aiSeries JSON PATH '$.aiSeries',
                                      iUser VARCHAR(255) PATH '$.iUser')) jsontable
HAVING bActive = true 
   AND sSubLocation = 'Mytestcase'

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bcf7f238e23a2c282cdea76c183ae8fa

answered May 19, 2021 at 5:30

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

3 Comments

amit chauhan Over a year ago

This seems not working on Mysql Version 5.7.4.

amit chauhan Over a year ago

Thanks Akina, but it have error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(test.value, '$[*]' COLUMNS (bActive BOOLEAN PATH '$.bActi' at line 3.

Akina Over a year ago

@amitchauhan Maybe your MySQL version is too ancient? you have not specified it..

Collectives™ on Stack Overflow

How To extract Value from Json multi dimension Array In MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related