MongoDB {"$exists": false} equivalent in Postgres

Question

MongoDB has an $exists operator to check if a key exists in the document or not. It works with true and false.

Postgres can also have JSON data stored. What is the Postgres equivalent for the following Mongodb Query?

db.myDocs.find_one({'myKey': { '$exists': False }})

Answer 1

If you want to check a top level field you can add a where clause:

select * from myDocs
where column_name is null
limit 1;

And if you want to check a subfield of a json field you can use the jsonb ? operator:

select * from myDocs
where column_name::jsonb ? 'myKey' is false -- or
-- where column_name::jsonb ? 'myKey' = false
limit 1;

jsonb ? text → boolean

Does the text string exist as a top-level key or array element within the JSON value?

Answer 2

assuming you have a table (note json type):

create table MY_TABLE (
    ID int not null,
    JSON_VALUE json not null 
);

with data:

insert into MY_TABLE (ID, JSON_VALUE) values (1, '{"myKey":"should be found", "otherKey":"123"}');
insert into MY_TABLE (ID, JSON_VALUE) values (2, '{"somethingElse":"should not"}');

you can use postgre json function to get all the IDs of

select ID from 
(select ID, json_extract_path(JSON_VALUE, 'myKey') AS keys from MY_TABLE) t

where keys is null -- equivalent to {'myKey': { '$exists': False }}
-- or
where keys is not null -- equivalent to {'myKey': { '$exists': True }}

to clarify the result of sub-query: select ID, json_extract_path(JSON_VALUE, 'myKey') AS keys from MY_TABLE would be:

id  keys
1   should be found
2   (null)

Answer 3

Example of table

CREATE TABLE example_table(
    id INT NOT NULL,
    json_data JSON NOT NULL
);

Query:

SELECT * FROM (SELECT id, json_extract_path(json_data, 'myKey') AS jsonKey
FROM example_table) t WHERE jsonKey IS NULL

similar to:

db.myDocs.find({'myKey': { '$exists': false }})

you can view more information on the commands for Postgres JSON