Reputation: 76
I want to select all rows in a table where my "object_details" JSON field contains one of the following keys: "subject", "Subject", "SUBJECT".
With the following sample rows:
datetime | object_details
---------------------+------------------------------------------------------------
2019-07-21T00:01:34Z | {"Sender": "[email protected]", "Subject": "First email!"}
2019-07-23T09:30:01Z | {"Sender": "[email protected]", "subject": "Second email!"}
2019-07-27T22:23:15Z | {"Sender": "[email protected]", "protocol": "SMTP"}
I'm expecting the result to provide only these rows:
datetime | object_details
---------------------+------------------------------------------------------------
2019-07-21T00:01:34Z | {"Sender": "[email protected]", "Subject": "First email!"}
2019-07-23T09:30:01Z | {"Sender": "[email protected]", "subject": "Second email!"}
Upvotes: 0
Views: 1374
Reputation: 375
Using MySQL this can be done using this syntax.
SELECT *
FROM the_table
WHERE LOCATE("subject", object_details) > 0 ;
PostgreSQL would be (PostgreSQL is case sensitive):-
SELECT *
FROM the_table
WHERE POSITION('subject' IN LOWER(object_details)) > 0;
Upvotes: -1
Reputation:
You can use the exists operator ?|
that takes an array of keys as its input for that:
select *
from the_table
where object_details ?| array['subject', 'Subject', 'SUBJECT'];
Upvotes: 3