Conditionally select values from an array in a nested JSON string in a Mysql database

Question

I am struggling to conditionally extract values from a nested JSON string in the Mysql table.

{"users": [{"userId": "10000001", "userToken": "11000000000001", "userTokenValidity": 1}, {"userId": "10000002", "userToken": "12000000000001", "userTokenValidity": 1}, {"userId": "10000003", "userToken": "13000000000001", "userTokenValidity": 0}]}

I want to select a userToken but only if the userTokenValidity is 1. So in this example only "11000000000001" and "12000000000001" should get selected.

This will extract the whole array ... how should I filter the result?

SELECT t.my_column->>"$.users" FROM my_table t;

Answer 1

SELECT CAST(value AS CHAR) output
FROM test
CROSS JOIN JSON_TABLE(test.data, '$.users[*]' COLUMNS (value JSON PATH '$')) jsontable
WHERE value->>'$.userTokenValidity' = 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4876ec22a9df4f6d2e75a476a02a2615