In MySQL, json_search fail with integer element in json_array

Question

I have a json_array [1, 2, 3, 3, 3], and I want to find out where how many the element '3' is.

For example,

json_search('[1, 2, 3, 3, 3]', 'all', 3) return null;
json_search('["1", "2", "3", "3", "3"]', 'all', '3') return ["$[2]", "$[3]", "$[4]"];

Therefore,

json_length(json_search('[1, 2, 3, 3, 3]', 'all', 3)) return null;

I want to 3

I’ve been looking all day, but I don’t know the solution and ask for help.

Does this answer your question? MYSQL Triggers: JSON_SEARCH an integer value in a json array of integers — Vytautas
– Vytautas, Commented Apr 17, 2020 at 6:25

Tim Biegeleisen · Accepted Answer · 2020-04-17 06:31:17Z

1

One option here, assuming you have just a single top level array of JSON integers, would be to use a regex replacement trick to count the number of 3's:

WITH yourTable AS (
    SELECT '[1, 2, 3, 3, 3]' AS array
)

SELECT
    LENGTH(array) - LENGTH(REGEXP_REPLACE(array, '\\b3\\b', '')) AS num_3
FROM yourTable;

This returns 3 as the length, which is correct.

answered Apr 17, 2020 at 6:31

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

serverwizard Over a year ago

I use mysql version 5.7, so I couldn't use the method you told, but I got a big idea and solved the problem. Thank you very much.

Tim Biegeleisen Over a year ago

@serverwizard Thanks for the vote, but if your answer is very different than what I posted, but it worked, you should consider answering your own question (yes--you can actually do this).

Collectives™ on Stack Overflow

In MySQL, json_search fail with integer element in json_array

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related