在以下条件下使用'null'::jsonb：

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

其他的答案，无论多么有效，都是不必要的复杂，隐藏了事物的本质。问题是'null'::jsonb与Postgres null不同：

select 'null'::jsonb is null;

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

你可以使用一个简单的btree索引，它可以支持第一个查询：

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

或者，gin索引可以支持@>运算符：

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

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

您可以使用'@〉'操作符来检查'{"data":null}'是否包含在data-〉'block'路径中。

SELECT *
FROM tab
WHERE (data->'block') @> '{"data":null}'

输出：

检查here演示。

使用两个 predicate 查看键是否存在 * 以及值是否为null

where jsonb_path_exists(data,'$.block.data') and 
jsonb_path_query_first(data,'$.block') ->> 'data' is null;

示例

with test as ( 
select 1 id, '{ "block": { "data": null, "timestamp": "1680617159" } }'::jsonb  as data union all
select 2 id, '{ "block": { "data": 1, "timestamp": "1680617159" } }'::jsonb as data union all
select 3 id, '{ "block": {  "timestamp": "1680617159" } }'::jsonb as data)
select t.id
from test t 
where jsonb_path_exists(data,'$.block.data') and 
jsonb_path_query_first(data,'$.block') ->> 'data' is null;

id|
--+
 1|

此查询将导致表的Seq Scan，如下所示

explain analyze
select t.id
from test t 
where jsonb_path_exists(data,'$.block.data') and 
jsonb_path_query_first(data,'$.block') ->> 'data' is null;

Seq Scan on test t  (cost=10000000000.00..10000003289.05 rows=167 width=4) (actual time=45.745..68.798 rows=1 loops=1)
  Filter: (jsonb_path_exists(data, '$."block"."data"'::jsonpath, '{}'::jsonb, false) AND ((jsonb_path_query_first(data, '$."block"'::jsonpath, '{}'::jsonb, false) ->> 'data'::text) IS NULL))
  Rows Removed by Filter: 100002
Planning Time: 0.079 ms
JIT:
  Functions: 4
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.714 ms, Inlining 8.161 ms, Optimization 26.128 ms, Emission 11.304 ms, Total 46.308 ms
 Execution Time: 69.586 ms  <<<--------------

如果定义了data键的行数非常少，则可以从索引中获益

CREATE INDEX idx_test2 ON test USING btree (((jsonb_path_exists(data,'$.block.data'))));

该计划使用了索引，并且性能更高

Bitmap Heap Scan on test t  (cost=551.35..2965.38 rows=167 width=4) (actual time=0.034..0.037 rows=1 loops=1)
  Filter: (jsonb_path_exists(data, '$."block"."data"'::jsonpath, '{}'::jsonb, false) AND ((jsonb_path_query_first(data, '$."block"'::jsonpath, '{}'::jsonb, false) ->> 'data'::text) IS NULL))
  Rows Removed by Filter: 1
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_test2  (cost=0.00..551.31 rows=50002 width=0) (actual time=0.025..0.025 rows=2 loops=1)
        Index Cond: (jsonb_path_exists(data, '$."block"."data"'::jsonpath, '{}'::jsonb, false) = true)
Planning Time: 0.164 ms
Execution Time: 0.052 ms  <<<--------------

• 使用的测试数据 *

create table test as
select 1 id, '{ "block": { "data": null, "timestamp": "1680617159" } }'::jsonb  as data, repeat('x',2000) pad union all
select 2 id, '{ "block": { "data": 1, "timestamp": "1680617159" } }'::jsonb as data, repeat('x',2000) pad union all
select 3 id, '{ "block": {  "timestamp": "1680617159" } }'::jsonb as data, repeat('x',2000) pad union all
select id+3, '{ "block": {  "timestamp": "1680617159" } }'::jsonb as data, repeat('x',2000) pad from generate_series(1,100000) as t(id )
;