How to query postgres for jsonb field that has a key set to a null value

Question

I have a table in postgres which has rows like the following in a jsonb column called data:

{ "block": { "data": null, "timestamp": "1680617159" } }
{"block": {"hash": "0xf0cab6f80ff8db4233bd721df2d2a7f7b8be82a4a1d1df3fa9bbddfe2b609e28", "size": "0x21b", "miner": "0x0d70592f27ec3d8996b4317150b3ed8c0cd57e38", "nonce": "0x1a8261f25fc22fc3", "number": "0x1847", "uncles": [], "gasUsed": "0x0", "mixHash": "0x864231753d23fb737d685a94f0d1a7ccae00a005df88c0f1801f03ca84b317eb", "gasLimit": "0x1388", "extraData": "0x476574682f76312e302e302f6c696e75782f676f312e342e32", "logsBloom": "0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000", "stateRoot": "0x2754a138df13677ca025d024c6b6ac901237e2bf419dda68d9f9519a69bfe00e", "timestamp": "0x55baa522", "difficulty": "0x3f5a9c5edf", "parentHash": "0xf50f292263f296897f15fa87cb85ae8191876e90e71ab49a087e9427f9203a5f", "sealFields": [], "sha3Uncles": "0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347", "receiptsRoot": "0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421", "transactions": [], "totalDifficulty": "0x239b3c909daa6", "transactionsRoot": "0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421"}, "transaction_receipts": []}

I'd like to write a SQL query which selects all rows that have a null value for block.data but does not select rows which do not have a data field.

I've tried the following, and they all have failed:

SELECT * FROM table WHERE data->>'block'->>'data' IS NULL;
SELECT * FROM table WHERE data->'block'->'data' IS NULL;
SELECT * FROM table WHERE jsonb_extract_path_text(data, 'block', 'data') IS NULL;

It seems that in all of these cases, if the data field isn't present, it passes the where clause.

Answer 1

Just use 'null'::jsonb in the condition:

select *
from my_table
where data->'block'->'data' = 'null'

Other answers, however effective, are unnecessarily complex and hide the essence of things. The problem is that the 'null'::jsonb is not the same as Postgres null:

select 'null'::jsonb is null;

 ?column?
----------
 f
(1 row)

You can use a simple btree index that can support the first query:

create index on my_table ((data->'block'->'data'));

Alternatively, the gin index can support the @> operator:

create index on my_table using gin ((data->'block'->'data'));

select *
from my_table
where data->'block'->'data' @> 'null';