How do I search [] in json by sql

Question

How do I search [] where in json like

{"buyer_uni_no":{"info":[],"Pos":[[0,0,0,0]]}}

And there are my sql query I try but didn't work.

select * from test where JSON_VALUE(myjson,'$.buyer_uni_no.info') = '[]'
select * from test where JSON_VALUE(myjson,'$.buyer_uni_no.info[0]') = ''

probably you need to use JSON_LENGTH, ie select * from test where JSON_LENGTH(myjson,'$.buyer_uni_no.info') = 0 — Iłya Bursov
– Iłya Bursov, Commented Apr 26, 2019 at 5:53
@IłyaBursov you should add that as an answer. Looks like that'll give OP the needed records. — zedfoxus
– zedfoxus, Commented Apr 26, 2019 at 5:58

Tim Biegeleisen · Accepted Answer · 2019-04-26 05:58:07Z

1

Try using the JSON_LENGTH function on the array:

SELECT *
FROM test
WHERE JSON_LENGTH(json->"$.buyer_uni_no.info") = 0;

Demo

Another way, checking for a literal empty array []:

SELECT *
FROM test
WHERE CAST(myjson->"$.buyer_uni_no.info" AS CHAR(50)) = '[]';

answered Apr 26, 2019 at 5:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Thanks for your answer! JSON_LENGTH is work! But (json->"$.buyer_uni_no.info") need to change to (json,'$.buyer_uni_no.info').

What I gave above should at least run on MySQL 8+. Maybe you are using an earlier version of MySQL where the JSON API be slightly different.