0

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 }})
ankitjaininfo
  • 11,961
  • 7
  • 52
  • 75
  • Please **[edit]** your question (by clicking on the [edit] link below it) and add some sample data (including table structures) and the expected output based on that data as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking text tables. (please [edit] your question - do **not** put code or additional information in comments) –  Feb 17 '22 at 10:21
  • this question seems to be answered already here: https://stackoverflow.com/questions/7471625/fastest-check-if-row-exists-in-postgresql – R2D2 Mar 06 '22 at 23:01
  • 1
    @R2D2 the question you mentioned is about checking a row by an id, the OP here wants to check if a key exists or not – Fraction Mar 07 '22 at 08:40
  • 1
    maybe this one then :) -> https://stackoverflow.com/questions/28921355/how-do-i-check-if-a-json-key-exists-in-postgres , something like: SELECT '{"key_a":1}'::jsonb ? 'key_a' – R2D2 Mar 07 '22 at 08:47
  • yes, in case the OP wants to check a subfield of a json field, in my answer I posted the equivalent query of `db.myDocs.findOne({'myKey': { '$exists': false }})` for both cases – Fraction Mar 07 '22 at 09:01

3 Answers3

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)
ezer
  • 984
  • 6
  • 10
0

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?

Fraction
  • 11,668
  • 5
  • 28
  • 48
0

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

Aymen
  • 1,476
  • 18
  • 28