mysql - 通过 MySQL 中的 bool 值查找 JSON 对象路径

Question

如何找到具有属性 "is_true": true 的对象的路径?

数据列中的 JSON:

[
  {
    "myData": {},
    "is_true": true
  },
  {
    "myData": {},
    "is_true": false
  },
  {
    "myData": {},
    "is_true": false
  }
]

失败的尝试:

SELECT JSON_SEARCH(data, "all", true) AS booleanObject FROM my_table

和...

SELECT JSON_SEARCH(data, "all", "true") AS booleanObject
SELECT JSON_SEARCH(data, "all", "%true%") AS booleanObject
SELECT JSON_SEARCH(data, "all", true) AS booleanObject
SELECT JSON_SEARCH(data, "all", 1) AS booleanObject
SELECT JSON_SEARCH(data, "all", true, NULL, "$") AS booleanObject

等等。

Answer 1

不幸的是它看起来像 JSON_SEARCH只搜索字符串值。你可以使用蛮力:

SELECT x.i, JSON_EXTRACT(data, CONCAT('$[', x.i, ']')) AS obj
FROM t
INNER JOIN (
    SELECT 0 AS i UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) AS x ON x.i < JSON_LENGTH(t.data)
WHERE JSON_EXTRACT(data, CONCAT('$[', x.i, '].is_true')) = true

如果您使用的是 MySQL 8，那么更优雅的选择是使用 JSON_TABLE :

SELECT x.i, x.obj
FROM t
CROSS JOIN JSON_TABLE(t.data, '$[*]' COLUMNS(
  i FOR ORDINALITY,
  is_true BOOLEAN PATH '$.is_true',
  obj JSON PATH '$'
)) AS x
WHERE x.is_true = true

Demo on db<>fiddle