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!

  • If you're interested in using JSON, wouldn't you be better of with a more recent version of MySQL or MariaDB? Not to mention PostgreSQL. – Gerard H. Pille Sep 15 '20 at 15:54
  • I'm on Aurora which doesn't have 8.0 version just yet :( – Nir Sep 16 '20 at 15:40

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.

  `id` INTEGER,
  `name` JSON
  (`id`, `name`)
  ('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

  • This is awesome! But your 4 value is different from mine and putting it in the fiddle actually returns it (which is "wrong") - dbfiddle.uk/… – Nir Sep 16 '20 at 15:42
  • The fiddle is using v8. :-( – Gerard H. Pille Sep 16 '20 at 15:48
  • if you change tio mysqlk 5.7 it works too, only older Version don't have json – nbk Sep 16 '20 at 19:56
  • Not sure what you mean, but if you change forth row with ('4', '{"vals":[{"id":222,"name":"this is the one"},{"id":123}],"additional":"text text"}'); - you will get extra row back – Nir Sep 16 '20 at 19:57
  • 1
    i know, that is why it is more than excessive to extract that information., you should try to use anther language like php or python c# c++ .... that can do much more with json, at least you have already eliminated some rows – nbk Sep 16 '20 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.