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

Question

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!"}

Please have a look at the second answer in the following link stackoverflow.com/questions/16082575/… — Tejus
– Tejus, Commented Jul 29, 2019 at 5:41
Possible duplicate of SQL select only rows with max value on a column — Sai
– Sai, Commented Jul 29, 2019 at 6:04
@SaiKrishna: that question has nothing to do with what Jed wants to achieve. — user330315
– user330315, Commented Jul 29, 2019 at 6:21

score 3 · Accepted Answer · 2019-07-29 06:21:23Z

3

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'];

edited Jul 29, 2019 at 6:21

answered Jul 29, 2019 at 5:59

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Over a year ago

Wouldn't this catch an edge case where the word subject is included in one of the stored json values, but not as any of the keys, or as part of a key name? If this is the case perhaps including the quotes and colon? array['"subject":', '"Subject":', '"SUBJECT"':] would force a more precise match?

user330315 Over a year ago

@Andrew: no, the exists operator only looks at the keys, never at the values.

Dixon · Accepted Answer · 2019-07-29 08:36:17Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related