Question 20454 in Mysql Question posted in
The official MySQL documentation can be found here.

Query data that have/don't have a json key – value pair – Mysql

I am having a table in my database in which there’s a field that holds data in a json format. How can I query records that have/don’t have a specific key-value pair in their json?

Something like this in pseudo code

SELECT * FROM `foo_db`.`foo_table` 
WHERE `foo_field` IN_JSON exists('bar_key') // or IN_JSON !exists('bar_key')
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    None of the solutions proposed worked for me, so I had to do a lot of searching, but in the end I found something that worked for me, so posting it here

    SELECT * FROM `foo_table`
WHERE JSON_EXTRACT(`foo_field`, '$.bar_key') IS NULL / IS NOT NULL;

  2. 0

    You could try JSON_SEARCH if you are looking for any string within the Json

    SELECT * FROM `foo_db`.`foo_table` 
WHERE JSON_SEARCH(`foo_field`, 'one', 'bar_key') IS NOT NULL
    Login or Signup to reply.
  3. 0

    You could use JSON_CONTAINS function.

    This function takes two arguments – the JSON document to search and the key-value to look for.

    SELECT * 
FROM foo_table
WHERE JSON_CONTAINS(foo_field, '{"bar_key": "bar_value"}');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search