CODEWITHSUNDEEP
sqljsonmariadb
TechEnthusiast
TechEnthusiast

Reputation: 1895

MariaDB JSON Functions with Arrays

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...

1

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... 

0

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?

Upvotes: 2

Views: 6325

Answers (1)

TechEnthusiast
TechEnthusiast

Reputation: 1895

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"}]

Upvotes: 5

Related Questions