CODEWITHSUNDEEP
sqljsonpostgresql
Rich
Rich

Reputation: 185

Query to determine if a JSON value contains a specified attribute

I want to query a table that has JSON data. The data of the column detail in the table details are as follows.

id     | 1

detail | {
   myCompany: [{ email: '[email protected]', inProfit: true }],
   myData: [
      { email: '[email protected]', personalUse: true },
      { email: '[email protected]', personalUse: false },
   ],
};

The JSON value of the column detail is as follows

{
   "myCompany":[
      {
         "email":"[email protected]",
         "inProfit":true
      }
   ],
   "myData":[
      {
         "email":"[email protected]",
         "personalUse":true
      },
      {
         "email":"[email protected]",
         "personalUse":false
      }
   ]
}

I want a query that fetches id if the passed email is present in myData and has true value for personalUse. The query doesn't return any id if the passed email is present but doesn't have personalUse as true.

I have used the below two queries but it will return id for both cases. 

SELECT id 
FROM details 
WHERE (detail->'myData')::JSONB @> '[{"email": "[email protected]"}, 
                                     {"personalUse": true}]'::JSONB;

SELECT id 
FROM details 
WHERE (detail->'myData')::JSONB @> '[{"email": "[email protected]"}, 
                                     {"personalUse": true}]'::JSONB;

Expected output is it should return id for first query and not return any id for second one.

You can use the below queries to try it locally.

CREATE TABLE details (id bigserial primary key, detail json not null);

INSERT INTO details (detail) 
VALUES 
  ('{"myCompany":[{"email":"[email protected]", "inProfit":true } ],  
     "myData":[{"email":"[email protected]", "personalUse":true }, 
               {"email":"[email protected]", "personalUse":false } ] }');

Upvotes: 0

Views: 254

Answers (1)

user330315
user330315

Reputation:

You are comparing an array with two elements (each with one key) in your queries, rather than an array with one element that contains two keys.

The array contains {"email": "[email protected]", "personalUse": true} data) and that is something different then {"email": "[email protected]"}, {"personalUse": true} - which is the value you compare against.

In your queries, the first json key/value pair (with the email address) matches in both values in the table and the contains operator @> only checks for at least one match.

The following queries work as expected:

SELECT id 
FROM details 
WHERE detail -> 'myData' @> '[{"email": "[email protected]", "personalUse": true}]';

SELECT id 
FROM details 
WHERE detail -> 'myData' @> '[{"email": "[email protected]", "personalUse": true}]';

(I removed the casts to jsonb for better readability)

Upvotes: 1

Related Questions