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": "bob@example.com", "Subject": "First email!"}   
2019-07-23T09:30:01Z | {"Sender": "carol@example.com", "subject": "Second email!"}
2019-07-27T22:23:15Z | {"Sender": "dave@example.com", "protocol": "SMTP"}

I'm expecting the result to provide only these rows:

datetime             | object_details                                             
---------------------+------------------------------------------------------------
2019-07-21T00:01:34Z | {"Sender": "bob@example.com", "Subject": "First email!"}   
2019-07-23T09:30:01Z | {"Sender": "carol@example.com", "subject": "Second email!"}

Please have a look at the second answer in the following link https://stackoverflow.com/questions/16082575/sql-ignore-case-while-searching-for-a-string — Tejus, Jul 29 '19 at 05:41
Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Sai, Jul 29 '19 at 06:04
@SaiKrishna: that question has nothing to do with what Jed wants to achieve. — , Jul 29 '19 at 06:21

score 3 · Accepted Answer · 2019-07-29T06:21:23.967

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 '19 at 06:21

answered Jul 29 '19 at 05:59

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? – Andrew Jul 29 '19 at 06:35
1

@Andrew: no, the exists operator only looks at the keys, never at the values. – Jul 29 '19 at 06:37

score -1 · Answer 2 · answered Jul 29 '19 at 08:36

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;

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

2 Answers2