Find JSON object path by boolean value from MySQL

Question

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.

Expected output: If search for "true" $[0].is_true else paths contain "false" values $[1].is_true, $[2].is_true etc. — Yurii Kosiak
– Yurii Kosiak, Commented Nov 27, 2019 at 12:07

Salman Arshad · Accepted Answer · 2019-11-27 10:25:04Z

2

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

answered Nov 27, 2019 at 10:25

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wchiquito · Accepted Answer · 2019-11-28 19:07:19Z

1

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.

answered Nov 28, 2019 at 19:07

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Comments

Meriton Reqica · Accepted Answer · 2019-11-27 10:17:51Z

0

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

answered Nov 27, 2019 at 10:17

Meriton Reqica

1168 bronze badges

3 Comments

Yurii Kosiak Over a year ago

I tried the same query but I have over 500k lines in the table and I didn't wait to response. Looks like this query too slow. Search for 1 line takes 0.007sec., 500.000 * 0.007 = 3500 sec... Also I would like to find true value in this way: JSON_EXTRACT(is_true, "$[*].is_true") = true but this query not allows to find data when used * sign.

Meriton Reqica Over a year ago

Did you try JSON_EXTRACT(is_true, "$.is_true") = true. And also dont return all of the data use limit and offset so you can paginate.

Yurii Kosiak Over a year ago

If I search for JSON_EXTRACT(data, "$[*].is_true") it returns rows "[true, false, false, false....]" else if I search for JSON_EXTRACT(data, "$[*].is_true") = true it always returns 0. But if I search for some string value JSON_SEARCH(JSON_EXTRACT(data, "$[*].string_value"), "all", "myString") all is correct and I receive result with collection of paths to properties like: ["$[1]", "$[2]", "$[3]", "$[4]", "$[5]", "$[6]"....] if I search for is_true property I always receive NULL result.

Collectives™ on Stack Overflow

Find JSON object path by boolean value from MySQL

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related