MariaDB JSON Functions with Arrays

Question

I have JSON arrays stored as strings in a MariaDB table. I want to return the rows when certain key-values are present anywhere in the array.

I performed the following test....

set @json='[{"name":"Albert","state":"IL"},{"name":"John","state":"CA"}]'

Executed the following query ...

select json_value(@json,'$[0].name')='Albert'

It got me the desired result which is...

Even the following query also gave me the same result(may be '*' is treated as the first element)...

select json_value(@json,'$[*].name')='Albert'

But when I provide the second name("John") in the condition, I do not get any result...

select json_value(@json,'$[*].name')='John'

Result...

So my observation is that when we provide the array index the "json_value" function is able to return the desired result.

Is there a way where I can avoid specifying the array index and search the desired key-value?

Try JSON_SEARCH, see dbfiddle.

wchiquito
– wchiquito

2017-12-10 17:43:04 +00:00
Commented Dec 10, 2017 at 17:43 — wchiquito
– wchiquito, Commented Dec 10, 2017 at 17:43

TechEnthusiast · Accepted Answer · 2017-12-12 08:26:20Z

5

As commented by @dbfiddle I tried using JSON_SEARCH function.

Here is what I tried to get the desired result.

create table label_test(labels VARCHAR(1000));

insert into label_test values ('[{"name":"Albert","state":"IL"},{"name":"John","state":"CA"}]');

select * from label_test where JSON_SEARCH(labels,'all','John') like '"$[%].name"';

The result is the whole row as expected.

[{"name":"Albert","state":"IL"},{"name":"John","state":"CA"}]

answered Dec 12, 2017 at 8:26

TechEnthusiast

1,8932 gold badges19 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

MariaDB JSON Functions with Arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related