如何在PostgreSQL中查询jsonb字段指定键值为null的行

问题描述 #

有一张表，其data列类型为jsonb，包含如下行数据：

{ "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":[]}

需要筛选出所有block.data字段存在且值为null的行，排除不存在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;

原因分析 #

当block对象中不存在data字段时，data->'block'->'data'会返回null，因此单纯的IS NULL判断会同时匹配两种情况：字段存在且值为null，以及字段不存在。必须额外增加字段存在的判断。

解决方案 #

方法1：使用jsonb_exists函数判断字段存在 #

SELECT * 
FROM your_table 
WHERE jsonb_exists(data->'block', 'data') 
  AND data->'block'->'data' IS NULL;

jsonb_exists函数用于检查指定jsonb对象中是否存在指定键，这里先确认block对象里有data字段，再判断其值为null。

方法2：使用?操作符判断字段存在 #

SELECT * 
FROM your_table 
WHERE (data->'block') ? 'data' 
  AND data->'block'->'data' IS NULL;

?操作符是jsonb_exists的简写形式，作用相同，检查data->'block'这个jsonb对象是否包含data键。

方法3：使用jsonb_typeof函数直接匹配 #

SELECT * 
FROM your_table 
WHERE jsonb_typeof(data->'block'->'data') = 'null';

jsonb_typeof会返回jsonb值的类型：

• 当block.data存在且值为null时，返回字符串'null'
• 当block.data不存在时，data->'block'->'data'为null，jsonb_typeof(null)返回null，不会匹配='null'的条件，刚好满足需求。

以上三种方法都能准确筛选出block.data存在且值为null的行，排除无data字段的行。

内容的提问来源于stack exchange，提问作者Paymahn Moghadasian