How to Query for Null Values in JSON Field Type Postgresql

Query of Postgresql JSON column containing array of objects returns nulls

You need first expands a JSON array to a set of JSON elements (you can use: json_array_elements(json)):

SELECT 
      data -> 'dateTime' AS datetime,
      data -> 'value' ->> 'bpm' AS bpm,
      data -> 'value' ->> 'confidence' AS confidence 
FROM (SELECT json_array_elements(data) AS data FROM heart_rate_json) AS sq;

Select records that have any null value on the Jsonb column

You can do this using a JSON path expression.

select *
from the_table
where jsonb_path_exists(the_column, '$.keyvalue() ? (@.value == null)')

Get records where json column key is null

use brackets (). Looks like compiler tries to see it like details->('email' IS NOT NULL). So you can fix it like this:

select *
from users
where (details->'email') is not null

sql fiddle demo

actually, to get records where details["email"] is null or email key doesn't exist, you can use this query:

select *
from users
where (details->>'email') is null

as described in this answer.

Why does JSON null not cast to SQL null in postgres?

Use the ->> operator for retrieving the json field.

This should work and return null (as in, no value) correctly for both:

select ('{"id": null}'::json->>'id')::text
select ('{"id": null}'::json->>'id')::integer

I've made a fiddle that demostrates it

PS: to get the string "null", you'd need to define your json as: {"id": "null"}

SELECT values which are not null from JSONB field in Postgres

I quickly tested your question and found no problem:

patrick@brick:~$ psql -d test
psql (9.5.0)
Type "help" for help.

test=# CREATE TABLE mytable (id serial PRIMARY KEY, data jsonb);
CREATE TABLE
test=# INSERT INTO mytable (data) VALUES
('{"ip": "192.168.0.1", "property": "router"}'),
('{"ip": "127.0.0.1", "property": "localhost"}'),
('{"ip": "192.168.0.15", "property": null}');
INSERT 0 3
test=# SELECT * FROM mytable;
 id |                     data
----+----------------------------------------------
  1 | {"ip": "192.168.0.1", "property": "router"}
  2 | {"ip": "127.0.0.1", "property": "localhost"}
  3 | {"ip": "192.168.0.15", "property": null}
(3 rows)

test=# SELECT data->>'property' FROM mytable WHERE data->>'property' IS NOT NULL;
 ?column?
-----------
 router
 localhost
(2 rows)

Note that in jsonb a NULL value should be specified precisely so on input (as in the sample above), not in some quoted version. If the value is not NULL but an empty string or something like '<null>' (a string) then you should adapt your test to look for that: WHERE data->>'property' = ''. If this is the case you could consider using jsonb_set() to set such values to a true json null.

Incidentally, you could also do:

SELECT data->>'property' FROM mytable WHERE data->'property' IS NOT NULL;

i.e. test the jsonb value for NULL rather than its cast to text. More efficient, certainly on larger tables. This obviously only works on true nulls.

Update JSONB column with NULL value in PostgreSQL

Concatenating anything with null produces null. You can use coalesce() or conditional logic to work around this:

SET x.data = COALESCE(y.data::jsonb || x.data, y.data::jsonb)

Or:

SET x.data = CASE WHEN x.data IS NULL 
    THEN y.data::jsonb
    ELSE y.data::jsonb || x.data
END

Note that there is no need to explictly cast x.data to jsonb, since it's a jsonb column already.

How to set a JSONB value to null

If you want the null value inside the JSONB, then it must be a JSON null value, not an SQL NULL value. JSON null must be spelled in lower case.

How to query a json column for empty objects?

There is no equality (or inequality) operator for the data type json as a whole, because equality is hard to establish. Consider jsonb in Postgres 9.4 or later, where this is possible. More details in this related answer on dba.SE (last chapter):

How to remove known elements from a JSON[] array in PostgreSQL?

SELECT DISTINCT json_column ... or ... GROUP BY json_column fail for the same reason (no equality operator).

Casting both sides of the expression to text allows = or <> operators, but that's not normally reliable as there are many possible text representations for the same JSON value. In Postgres 9.4 or later, cast to jsonb instead. (Or use jsonb to begin with.)

However, for this particular case (empty object) it works just fine:

select * from test where foo::text <> '{}'::text;