CODEWITHSUNDEEP
mysqlmysql-8.0
Tobias Lindberg
Tobias Lindberg

Reputation: 3

Can't search values in a json column when it contains a key value object in MySQL 8.0

I'm having a problem searching for a value in a JSON column in MYSQL 8.

The goal is to select all records where at least one of the markets has a value of true, see below for an example of what the column value might look like.

{"at": false, "au": false, "be": false, "ch": false, "cz": false, "de": false, "dk": false, "es": false, "eu": false, "fi": false, "fr": false, "gl": false, "hk": false, "it": false, "jp": false, "kr": false, "nl": false, "no": false, "se": false, "uk": true, "us": false}

We've tried using JSON_CONTAINS as well as JSON_SEARCH but end up with 0 results (Can't include all queries we've tried as we haven't saved them). Also can't seem to find anyone that has tried doing this with a similarly structured object/query. Is this query possible in MySQL?

The original query we hoped would work:

SELECT *
FROM
    `redirects`
WHERE
    JSON_CONTAINS(`synced_markets`, 'true')

Update

We managed to hack it a bit since we were only concerned if at least one was true by casting it to a char, does not feel like it's a proper solution though.

WHERE CAST(`synced_markets` AS CHAR) LIKE '%true%'

Upvotes: 0

Views: 850

Answers (1)

lyhADT
lyhADT

Reputation: 36

I think you misunderstood the JSON_CONTAINS function. It checks the json string as first argument wether contains the key-value as second argument.Not just the value.The correct usage likes below:

SELECT JSON_CONTAINS('{"a": true, "b": false}', '{"a":true}');

The command returns 1.

Read the manual for more info: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Maybe you can just treat the column value as string. And just

SELECT *
FROM
    `redirects`
WHERE
    `synced_markets` like '%true%'

Upvotes: 2

Related Questions