Search for a value in a JSON field in MYSQL

Question

I am trying to understand the "new" MYSQL JSON field.

I have this table:

id (int-11, not_null, auto_inc)
customer_id (int-11, not null)
labels (json)

With this data:

id: 1
customer_id: 1
labels: [{"isnew": "no", "tagname": "FOO", "category": "CAT_1", "isdeleted": "no"}, {"isnew": "yes", "tagname": "BAR", "category": "CAT_2", "isdeleted": "no"}]

JSON beautify

[
  {
    "tagname": "FOO",
    "category": "CAT_1",
    "isnew": "no",
    "isdeleted": "no"
  },
  {
    "tagname": "BAR",
    "category": "CAT_2",
    "isnew": "yes",
    "isdeleted": "no"
  }
]

And now I want to SELECT all the customers (by customer_id) in the table that have a specific category and a specific tagname

I tried this one:

SELECT * FROM labels_customers_json
WHERE JSON_SEARCH(labels, 'all', 'BAR') IS NOT NULL

But this is not what I want. This one is searching in every json attribute. I have seen some examples of JSON_EXTRACT:

SELECT * FROM `e_store`.`products`
WHERE
    `category_id` = 1
    AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
    AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

SELECT c, c->"$.id", g, n
FROM jemp
WHERE JSON_EXTRACT(c, "$.id") > 1
ORDER BY c->"$.name";

So I tried this

SELECT * FROM labels_customers_json
WHERE JSON_EXTRACT(labels, '$.tagname') = 'BAR'

SELECT labels, JSON_EXTRACT(labels, "$.customer_id"), customer_id
FROM labels_customers_json
WHERE JSON_EXTRACT(labels, "$.customer_id") > 0

It has the correct version now, 5.7.22. My phpmyadmin was obsolete, it is 4.8.2, but still having the same problem — Jordi Huertas
– Jordi Huertas, Commented Jul 31, 2018 at 9:44

Halko Karr-Sajtarevic · Accepted Answer · 2018-07-31 11:25:36Z

2

You could probably try using SELECT * FROM labels_customers_json WHERE JSON_SEARCH(labels, 'all', "BAR", NULL, "$[*].tagname") is not null - although i cannot say if that is the best way to perform this query.

edited Jul 31, 2018 at 11:25

answered Jul 27, 2018 at 16:18

Halko Karr-Sajtarevic

2,2781 gold badge17 silver badges14 bronze badges

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

3 Comments

Jordi Huertas Over a year ago

Like this? SELECT * FROM labels_customers_json WHERE "TEST" IN JSON_EXTRACT (labels, "$[*].tagname"). It returns a syntax error near 'JSON_EXTRACT...

Jordi Huertas Over a year ago

It works, but you both answer the same. I do not know who was first to give the reptutation.

Halko Karr-Sajtarevic Over a year ago

according to the timestamps, mine was 4 seconds before, but sebastians is more detailed

Sebastian Brosch · Accepted Answer · 2018-07-31 11:42:40Z

1

+50

You can use JSON_SEARCH to search on a specific path too. So you can use the following query:

SELECT * 
FROM labels_customers_json 
WHERE JSON_SEARCH(labels, 'all', 'BAR', NULL, '$[*].tagname') IS NOT NULL

You can also use JSON_EXTRACT and JSON_CONTAINS together:

SELECT *
FROM labels_customers_json
WHERE JSON_CONTAINS(JSON_EXTRACT(labels, '$[*].tagname'), '["BAR"]') > 0;

You can also use only JSON_CONTAINS to check:

SELECT *
FROM labels_customers_json
WHERE JSON_CONTAINS(labels, '{"tagname":"BAR"}') > 0;

demos: https://www.db-fiddle.com/f/rufrThAQPfXHrK9YyibFSm/2

edited Jul 31, 2018 at 11:42

answered Jul 31, 2018 at 11:25

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

4 Comments

Jordi Huertas Over a year ago

It works, but you both answer the same. I do not know who was first to give the reptutation.

Sebastian Brosch Over a year ago

@JordiHuertas that have a specific category and a specific tagname - on the same object or can be different objects?

Jordi Huertas Over a year ago

It can have different. This is a tag system to label the customers. So a tagname can have a different category in many customers. For example, it should let me find a customer that have a label "unsuscribed" from a category "email" or "postal_mail".

Jordi Huertas Over a year ago

I was doing this saving JSON in a TEXT field, but now is so complicated to work all the JSON with PHP.

Collectives™ on Stack Overflow

Search for a value in a JSON field in MYSQL

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related