Mysql – Search in JSON by id and name

jsonMySQLmysql-5.7queryselect

I have this table:

id number
name text

row for example:

id name
1 {"vals":[{"id":123,"name":""}],"additional":"text"}
2 {"vals":[{"id":123}],"additional":"text text"}
3 {"vals":[{"id":222},{"id":123,"name":"this is the one"}],"additional":"text text"}
4 {"vals":[{"id":222,"name":"this is the one"},{"id":123}],"additional":"text text"}

I want to return the ids where vals[*].id=123 and their other name element is not empty(not other name in the json).
So only this one will turn:

    3 {"vals":[{"id":222},{"id":123,"name":"this is the one"}],"additional":"text text"}

I'm not strong with json type queries so I only got:

select id 
from tab
where name ->>"$.vals[0].id"=123

I should have done "$.vals[*].id", but that returns to me [123] and that is not = 123

Appreciate your help!

Best Answer

JSON is always tricky and you have to search a lot of tutorials to get the hang of it.

But you can use normalized table instead of JSON.

CREATE TABLE tab (
  `id` INTEGER,
  `name` JSON
);

INSERT INTO tab
  (`id`, `name`)
VALUES
  ('1', '{"vals":[{"id":123,"name":""}],"additional":"text"}'),
  ('2', '{"vals":[{"id":123}],"additional":"text"}'),
  ('3', '{"vals":[{"id":123,"name":"this"}],"additional":"text"}'),
  ('4', '{"vals":[{"id":222,"name":"this"}],"additional":"text"}');

SELECT id, name
FROM tab WHERE JSON_CONTAINS(name->>'$.vals[*].id','123','$') 
AND (name->>'$.vals[*].name' IS NOT NULL AND  NOT `name`->'$.vals[*].name' = JSON_ARRAY("")  )
;

id | name                                                         
-: | :------------------------------------------------------------
 3 | {"vals": [{"id": 123, "name": "this"}], "additional": "text"}

db<>fiddle here

Related Question