CODEWITHSUNDEEP
mysqlmysql-json
Fabrício
Fabrício

Reputation: 1090

Query a JSON column with an array of object in MySQL

I have a json column with the follow array:

[
  {
    "id": "24276e4b-de81-4c2c-84e7-eed9c3582a31",
    "key": "id",
    "type": "input",
  },
  {
    "id": "e0ca5aa1-359f-4460-80ad-70445be49644",
    "key": "name",
    "type": "textarea",
    }
]

I tried the follow query to get the row that has the id 24276e4b-de81-4c2c-84e7-eed9c3582a31 in the document column, but it returns not results: 

select * from jobs WHERE document->'$[*].id' = "24276e4b-de81-4c2c-84e7-eed9c3582a31"

Anyone know how to do the right query?

Upvotes: 36

Views: 46953

Answers (4)

user5134456
user5134456

Reputation:

Try like this:

SELECT * FROM jobs WHERE document->'$[*].id' = json_array("24276e4b-de81-4c2c-84e7-eed9c3582a31");

It works for me, but I think the below way is more better:

SELECT * FROM jobs WHERE json_contains(document->'$[*].id', json_array("24276e4b-de81-4c2c-84e7-eed9c3582a31"));

Actually It's easy just remember the return value is JSON_TYPE but not a String or something else;

Upvotes: 24

GeekLei
GeekLei

Reputation: 1001

maybe this? @Barmar

SELECT * FROM jobs WHERE JSON_SEARCH(document, "one", "24276e4b-de81-4c2c-84e7-eed9c3582a31", NULL, '$[*].id') IS NOT NULL;

Upvotes: 5

Dina
Dina

Reputation: 1015

I use mysql 5.7 and so JSON_CONTAINS can be easily used like this:

SELECT JSON_CONTAINS(
                '[{"id": "24av","name": "she"},{"id": "e0c2", "name": "another_she"}]', 
                JSON_OBJECT('id', "e0c2")
                );

Upvotes: 44

Barmar
Barmar

Reputation: 782344

When you use document->'$[*].id' it returns a comma-delimited list of all the ID properties. This won't be equal to the value of just one ID string, unless there's only one object in the document column.

You need to use JSON_SEARCH() to search for a matching element within the JSON value.

SELECT * 
FROM jobs 
WHERE JSON_SEARCH(document, "one", "24276e4b-de81-4c2c-84e7-eed9c3582a31", NULL, '$[*].id');

Upvotes: 4

Related Questions