0

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!

2
  • 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. Commented Sep 15, 2020 at 15:54
  • I'm on Aurora which doesn't have 8.0 version just yet :(
    – Nir
    Commented Sep 16, 2020 at 15:40

2 Answers 2

0

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

7
  • 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
    Commented Sep 16, 2020 at 15:42
  • The fiddle is using v8. :-( Commented Sep 16, 2020 at 15:48
  • if you change tio mysqlk 5.7 it works too, only older Version don't have json
    – nbk
    Commented Sep 16, 2020 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
    Commented Sep 16, 2020 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
    Commented Sep 16, 2020 at 20:12
0

Using MySQL 8.0.34 and data from the question post (note the JSON data is of type JSON instead of TEXT, and the column name is changed to data from name):

CREATE TABLE tab (
  id INT,
  data JSON);

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

The following query returns the expected result:

SELECT tab.id, ANY_VALUE(tab.data) AS data
FROM 
  tab, 
  JSON_TABLE(data->"$.vals", '$[*]' COLUMNS(id INT PATH '$.id', name CHAR(30) PATH '$.name')) AS jtab 
WHERE 
  jtab.id = 123 AND
  LENGTH(jtab.name) > 0
GROUP BY tab.id;

+------------------------------------------------------------------------------------------------------------
| id   | data
+------+-----------------------------------------------------------------------------------------------------
|    3 | {"vals": [{"id": 222}, {"id": 123, "name": "** this is the one **"}], "additional": "text text 3"} 
+------+-----------------------------------------------------------------------------------------------------

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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