有条件地从Mysql数据库中嵌套JSON字符串中的数组中选择值

Question

我正在努力有条件地从Mysql表中嵌套的JSON字符串中提取值。

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

我想选择userToken，但仅当userTokenValidity为1时。所以在这个例子中只有"11000000000001"one_answers"12000000000001";

这将提取整个数组…我应该如何过滤结果?

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&小提琴= 4876 ec22a9df4f6d2e75a476a02a2615