Как запросить postgres для поля jsonb, ключ которого имеет нулевое значение

Вы можете использовать оператор «@>», чтобы проверить, содержится ли '{"data":null}' в пути data-> «block».

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

Выход:

Посмотрите демо здесь.

Используйте два предиката, чтобы увидеть, существует ли ключ * и является ли значение 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 )
;

Просто используйте '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';