0

I have this table currently

ID | Seller_Name | Seller_Non_Working_Day
1 | Seller A | [1,7]
2 | Seller B | [1]
3 | Seller C | []
4 | Seller D | [1,7]

I'm trying to extract seller who are not working on Sundays only, aka with [1] in the Seller_Non_Working_Day. This field is a JSON type.

This is my query, and I'm not getting any response :(

select * from table_name
where Seller_Non_Working_Day IN ('[1]')

Able to assist pls?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Azhar Mohammad
  • 25
  • 1
  • 3
  • 1
    This should help https://stackoverflow.com/questions/36249828/how-to-search-json-array-in-mysql – my_workbench Aug 05 '20 at 03:33
  • *I'm trying to extract seller who are not working on Sundays only, aka with [1] in the Seller_Non_Working_Day.* I.e. the outpout must contain only the row with `ID = 2`? if so use direct compare `WHERE Seller_Non_Working_Day = '[1]'`. – Akina Aug 05 '20 at 04:36

1 Answers1

0

MySQL JSON_OVERLAPS function compares two doc_path or arrays.It returns 0 if value do not match and 1 if it does.

JSON_OVERLAPS(doc_path1, doc_path2)

For your query, I have created a second array with one value [1] i.e. for Sunday. This will compare all records and return 0 and 1.Query result for sellers working on Sundays will return 1. To eliminate non-required records which are returning 1, I have added a WHERE clause. Here is the query:

SELECT * FROM table_name 
WHERE JSON_OVERLAPS(Seller_Non_Working_Day , '[1]') != 1 ;

There is another function JSON_CONTAINS which will list all records containing the specific value e.g. [1] at any position within JSON array. The syntax for JSON_CONTAINS is

JSON_CONTAINS(target, candidate[, path])

target is a specific field. Here it would be Seller_Non_Working_Day
candidate is a specific value to find. Here it would be [1]
Path is any specific array location which is optional.

One can use below query to fetch all sellers which are working on Sundays.

SELECT * FROM table_name 
WHERE JSON_CONTAINS(Seller_Non_Working_Day , '[1]');
my_workbench
  • 300
  • 3
  • 8
  • OP needs *extract seller who are not working on Sundays **only*** whereas your query returns the rows regardless of whether there are other values. – Akina Aug 05 '20 at 04:37
  • Hi when i tried the second option: SELECT * FROM table_name WHERE JSON_CONTAINS(Seller_Non_Working_Day , '[1]'); It returns me records with [1] and also [1,7]. I want only the records with [1] only – Azhar Mohammad Aug 05 '20 at 11:20