MySQL JSON - SELECT WHERE

Question

I'm trying to SELECT objects base on the roles property values.

Example: Select all names where role is 1 //response would return danny

Query Statement:

SELECT JSON_EXTRACT(username,'$[*].name') FROM objects WHERE JSON_CONTAINS(username,'1','$[*].roles')

COLUMN: username (JSON)

[
    {
        "name":"jordan",
        "roles":[1,2,5]
    },
    {
        "name":"danny",
        "roles":[1,4]
    }
]

Question: Why isn't my statement returning just the first object containing the name danny?

score 0 · Answer 1 · edited May 31 '17 at 19:00

0

Try the following:

SELECT * 
FROM objects 
WHERE JSON_CONTAINS(components, '1', '$.roles');

edited May 31 '17 at 19:00

xsami

answered May 31 '17 at 17:15

It needs a `WHERE` clause – May 31 '17 at 17:23
Yes, you have to add the condition in where clause only – Hariraman Radhakrishnan May 31 '17 at 17:28
It's not returning anything, this is an update so far `SELECT JSON_EXTRACT(components,'$[*].elements') FROM objects WHERE JSON_CONTAINS(components,'1','$.roles');` – May 31 '17 at 17:34
The statement is saying `SELECT` all `components->elements` FROM objects (table) WHERE the property `role` equals `1` – May 31 '17 at 17:35
@xsami yeah very close! the problem I'm testing now is that it can't do multiple. example - `JSON_CONTAINS(components, '1,2', '$.roles');` or `JSON_CONTAINS(components, JSON_ARRAY(1,2) , '$.roles');` the return on multiple like `1,2` is strict and has to be in order unlike a `IN()` statement which would test each element of the array separately not linear. – May 31 '17 at 19:53

1 Answers1