CODEWITHSUNDEEP
sqljsonpostgresql
Jed Mitten
Jed Mitten

Reputation: 76

How do I select all rows that have a particular key in a JSON field?

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

Answers (2)

Dixon
Dixon

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

user330315
user330315

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

Related Questions