SQL: where json field in (,..,)

user_18091497 0
Viewed 33

I've been trying tens of different combinations with no success.
I have a table policies, in which one column is id and one column is created_for, which is itself a json.
The json looks like this: created_for: {"user_email": "hello@gmail.com", "project_serial": "K8BUADS001="}.

Now I want to get all id in which the project_serial is in certain values.

What I tried:

SELECT p.id 
FROM policies as p 
WHERE JSON_VALUE(created_for, '$.project_serial') IN ('PROJIUHS6W001=', 'U5LQU13BIBN001=')

SELECT p.id 
FROM policies as p 
WHERE JSON_VALUE(created_for, '$.project_serial') IN ("PROJIUHS6W001=', 'U5LQU13BIBN001=")

SELECT p.id 
FROM policies as p 
WHERE created_for->>'$.project_serial' IN ('PROJIUHCHQ62UJ0KSS6W001=', 'PROJPPF4UE5LQU13BIBN001=')

SELECT p.id 
FROM policies as p 
WHERE created_for->>'$.project_serial' IN ("PROJIUHCHQ62UJ0KSS6W001=", "PROJPPF4UE5LQU13BIBN001=")

and just any other combination of double/single quotes on every parameter. All of these give me syntax error, I've been getting a lot of: syntax error near unexpected token '(', but also others as I tried many combinations.

Anyone knows what's the correct way of doing this?

syntax-error mysql sql json
1 Answers

This will work:

SELECT 
p.id FROM policies as p WHERE  json_extract(created_for, '$.project_serial') IN 
('PROJIUHS6W001=', 'U5LQU13BIBN001=')
user_62856000
Related