MySQL Return JSON array index based on property value

Question

I have a table with JSON data like this:

{"a": [{"color": "blue", "value": 15}, {"color": "red", "value": 30}]}

I need to get the "value" that is inside the same object of "blue".

I thought to use the code below:

SELECT JSON_EXTRACT(my_data, '$.a[0].value');

The problem is that the "blue" object can be in any index of the array.

So, is there a way to retrieve the index first and then i'll query using the right index?

UPDATE

The Barmar's answer works but it needs to wrap in JSON_UNQUOTE()

Could you post the full solution with markup and everything? — William Larsen Bang
– William Larsen Bang, Commented Jan 23, 2020 at 10:45

Barmar · Accepted Answer · 2022-02-11 17:34:23Z

10

Use JSON_SEARCH() to find the path to blue.

SELECT JSON_EXTRACT(my_data, JSON_UNQUOTE(REPLACE(JSON_SEARCH(my_data, 'one', 'blue'), '.color', '.value')))

JSON_SEARCH will return a string like $.a[0].color. REPLACE changes that to $.a[0].value, then you extract that element.

DEMO

edited Feb 11, 2022 at 17:34

answered May 17, 2019 at 21:33

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

16 Comments

Guilherme Over a year ago

Thank you for the reply. Did you test it? Because i'm receiving "Invalid JSON path expression. The error is around character position 1.". But i'll try to work it around with your explanation

Barmar Over a year ago

No, I don't have a version of MySQL with JSON support.

Barmar Over a year ago

It seems like the JSON path has to be a literal, you can't use an expression that calculates the path.

Barmar Over a year ago

Just another reason to avoid using JSON in MySQL. It denormalizes your schema, and the functions are really cumbersome and limited.

Guilherme Over a year ago

Your answer was usefull and it worked wraping the sentence in JSON_UNQUOTE()

|

Bill Karwin · Accepted Answer · 2019-05-26 15:50:50Z

3

Here's an example of using JSON_TABLE():

select j.* from d, json_table(d.data, '$.a[*]' columns (
  color varchar(20) path '$.color', 
  value int path '$.value')
) as j;
+-------+-------+
| color | value |
+-------+-------+
| blue  |    15 |
| red   |    30 |
+-------+-------+

You can then apply conditions in the WHERE clause, as if you had stored the data in a normal table.

select j.* from d, json_table(d.data, '$.a[*]' columns (
  color varchar(20) path '$.color', 
  value int path '$.value')
) as j 
where j.color = 'blue';
+-------+-------+
| color | value |
+-------+-------+
| blue  |    15 |
+-------+-------+

This requires you to write a complex query like this EVERY TIME you query the JSON data.

One wonders if it would have been easier to store the JSON in a normal table from the start.

I often recommend to MySQL users that storing data as JSON makes more work for you, if you need to make SQL expressions to reference individual fields within the JSON. I wouldn't use JSON in these cases, I'd explode the JSON array into rows, and the JSON fields into columns of a set of normal tables. Then you can write simpler queries, you can optimize with indexes, and you can use constraints and data types properly.

JSON is the most easily misused feature of the recent MySQL releases.

answered May 26, 2019 at 15:50

Bill Karwin

568k87 gold badges711 silver badges872 bronze badges

4 Comments

Barmar Over a year ago

I agree with your assessment of the usefulness of the JSON datatype. Theoretically it probably seemed like a good idea, but in practice it seems like a nightmare (not speaking from personal experience, just from trying to answer questions on SO).

Bill Karwin Over a year ago

JSON was implemented to satisfy market demand. MongoDB has it, PostgreSQL has it, even Informix has a BSON data type. Developers keep demanding "flexibility" to pour unstructured data into their database, because they can't define a concrete schema. Often it isn't their fault either, because they're working on a product specification that won't commit to enough concrete detail from which to define a schema.

Barmar Over a year ago

Yeah, I understand why compromises like this happen. Well, it's better than something I saw in the Invision Power Board source code several years ago -- they store some options in PHP serialize() format, and use LIKE or RLIKE to search for matches.

Bill Karwin Over a year ago

Ouch. That sounds like a classic example of the problem with regular expressions.

Collectives™ on Stack Overflow

MySQL Return JSON array index based on property value

2 Answers 2

16 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related