CODEWITHSUNDEEP
mysqlsqljson
Yurii Kosiak
Yurii Kosiak

Reputation: 410

Find JSON object path by boolean value from MySQL

How can I find path to object with property "is_true": true?

JSON in data column:

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

Failed attempts:

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

and...

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

etc.

Upvotes: 4

Views: 1840

Answers (3)

wchiquito
wchiquito

Reputation: 16551

This is an unintuitive option and you should be careful with performance issues:

SELECT
  JSON_SEARCH(
    REPLACE(
      REPLACE(
        REPLACE(
          JSON_EXTRACT(@`json`, '$[*].is_true'),
          ', ',
          '", "'
        ),
        '[',
        '["'
      ),
      ']',
      '"]'
    ), 'all', 'true') `positions`;

See dbfiddle.

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272136

Unfortunately it looks like JSON_SEARCH only searches string values. You could use brute force:

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

If you're using MySQL 8 then a more elegant option is to use 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

Upvotes: 2

Meriton Reqica
Meriton Reqica

Reputation: 116

SELECT JSON_EXTRACT(is_true, "$[0].is_true") AS is_true
FROM table
WHERE JSON_EXTRACT(is_true, "$[0].is_true") = 1

Upvotes: 0

Related Questions