如何在postgres中查询jsonb字段的键设置为空值

Question

我在postgres中有一个表，其中jsonb列中称为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": []}

我想写一个SQL查询，选择所有行有null值的block.data，但不选择行没有data字段。

我尝试了以下方法，但都失败了:

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;

似乎在所有这些情况下，如果data字段不存在，它传递where子句。

Answer 1

仅在条件

中使用'null'::jsonb

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

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

不一样

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';

Answer 2

您可以使用'@>'运算符检查'{"data":null}'是否包含在数据>'块'路径中。

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

:

<表类>数据tbody><<tr>{"block"{"data"零,"timestamp":"1680617159"}}

使用两个谓词查看键是否存在*以及值是否为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 )
;