1

I want to search product id from JSON filed but I am not able to search from JSON filed. In JSON field value added in multilevel like the below image.

enter image description here

I am able to search data from other filed like

SELECT equip_id FROM ' . $table. ' where  JSON_CONTAINS(category,["1"])

enter image description here

I want to search product by productID, Please let me know how can I achieve this.

Below are the full image of my table enter image description here

Pushpendra Singh
  • 182
  • 1
  • 1
  • 13
  • 2
    Out of curiosity, why did you store this data in JSON if you wanted to query it like normal data? It would be a lot simpler to write the SQL queries if you used normal rows instead of JSON arrays, and normal columns instead of JSON object fields. – Bill Karwin Apr 08 '21 at 15:00
  • Absolutely unclear. Provide sample data as CREATE TABLE + INSERT INTO scripts, not as pictures, and show desired output for this sample data. – Akina Apr 08 '21 at 15:07
  • Not JSON_CONTAINS but JSON_EXTRACT by path then compare. – Akina Apr 08 '21 at 15:08
  • @BillKarwin, It's my requirement that' why I am saving data in same field – Pushpendra Singh Apr 08 '21 at 15:15
  • @Akina, these(Category and Product) are my JSON fields in the database. can you please explain more about JSON_EXTRACT? – Pushpendra Singh Apr 08 '21 at 15:16

2 Answers2

2

I created a table for testing:

mysql> select * from mytable;
+----------------------------------------------------------------------+------------+
| product                                                              | category   |
+----------------------------------------------------------------------+------------+
| [{"OrderId": 1, "productID": "1"}]                                   | ["2", "3"] |
| [{"OrderId": 2, "productID": "1"}]                                   | ["2", "3"] |
| [{"OrderId": 3, "productID": "1"}]                                   | ["2", "3"] |
| [{"OrderId": 4, "productID": "1"}]                                   | ["2", "3"] |
| [{"OrderId": 5, "productID": "1"}]                                   | ["2", "3"] |
| [{"OrderId": 6, "productID": "1"}, {"OrderID": 7, "productID": "2"}] | ["2", "3"] |
+----------------------------------------------------------------------+------------+

Now I want to check if any product has an order for productID 2?

mysql> select json_contains(json_extract(product, '$[*].productID'), '["2"]') as `product_2_present` from mytable;
+-------------------+
| product_2_present |
+-------------------+
|                 0 |
|                 0 |
|                 0 |
|                 0 |
|                 0 |
|                 1 |
+-------------------+
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • You solution is giving output, can we add where close in a query like where product_2_present=1 because I want only that which have records not all – Pushpendra Singh Apr 08 '21 at 16:02
  • Yes, go ahead and try it. But you can't make reference to a column alias in the WHERE clause of the same query that defines it, so you'll have to put this query into a subquery. – Bill Karwin Apr 08 '21 at 16:08
1

I have created a temp table with the below data.

enter image description here

Output when I used below query for above collection

select * from table_name where json_contains(product, '{"productID" : "1"}')

enter image description here

Pushpendra Singh
  • 182
  • 1
  • 1
  • 13
Yogita
  • 170
  • 9