0

I have a table that looks like:

        id  ConsumerID                                    StoredFileLinks                                           Response  ... Active Archived CreatedDate         UpdatedDate
1255  9591          -1  [{"OriginalImage": "s3://crackd/inference_v2/5...  {"success": true, "message": "Device has passe...  ...      1        0        None 2019-10-30 11:11:09
1256  9590       22417  [{"OriginalImage": "s3://crackd/inference_v2/A...  {"success": true, "message": "Unable to detect...  ...      1        0        None 2019-10-30 10:30:42
1257  9589       22417  [{"OriginalImage": "s3://crackd/inference_v2/7...  {"success": true, "message": "Unable to detect...  ...      1        0        None 2019-10-30 10:30:25
1258  9588       22417  [{"OriginalImage": "s3://crackd/inference_v2/6...  {"success": true, "message": "Unable to detect...  ...      1        0        None 2019-10-30 10:30:13
1259  9587       22417  [{"OriginalImage": "s3://crackd/inference_v2/U...  {"success": true, "message": "Unable to detect...  ...      1        0        None 2019-10-30 10:30:06

Now I want to fetch all the rows whose "message" part under Response column doesn't contain the string, "Unable to..."

Query that I'm trying:

select * from table_name where StoredFileLinks is NOT NULL and 
Response->'$.message' NOT LIKE 'Unable to detect%' or 
Response->'$.message' NOT LIKE 'Your device is not%' order by RequestTime desc;

But it's still fetching me those rows whose Response column contains a message: 'Unable to."

Where am I messing up?

Ajay Bhagchandani
  • 135
  • 2
  • 20
  • Operator precedence... and is higher than or so you need `()` around the `OR` condition i.e. `StoredFileLinks is NOT NULL and (Response->'$.message' NOT LIKE 'Unable to detect%' or Response->'$.message' NOT LIKE 'Your device is not%')` – Nick Nov 14 '19 at 06:33
  • Modified it as ```select * from crack_detection_result where StoredFileLinks is NOT NULL and (Response->'$.message' NOT LIKE 'Unable to detect%' or Response->'$.message' NOT LIKE 'Your device is not%') order by RequestTime desc;``` Still not working. :( @Nick – Ajay Bhagchandani Nov 14 '19 at 06:39
  • Just to check - are you also trying to exclude responses which contain `'Your device is not%'` or do you want those messages to appear – Nick Nov 14 '19 at 06:41
  • I want to exclude the entire row that contains either of the messages: ```'Your device is not%'``` or ```Unable to detect%``` in Response column – Ajay Bhagchandani Nov 14 '19 at 06:45
  • 1
    I missed the `NOT` in your original query. Either change the `or` in your original query to `and` or use `StoredFileLinks is NOT NULL and NOT (Response->'$.message' LIKE 'Unable to detect%' or Response->'$.message' LIKE 'Your device is not%')` – Nick Nov 14 '19 at 06:47
  • 1
    You need `Response->>'$.message'` to unquote the value – Nick Nov 14 '19 at 06:53
  • Yup it worked now. Thanks alot. :) – Ajay Bhagchandani Nov 14 '19 at 06:54
  • Glad to hear it. – Nick Nov 14 '19 at 06:55

0 Answers0