sqlite 从查询结果中的json对象中删除值为‘NULL’的K，V对

ht4b089n 于 2022-11-14 发布在 SQLite

关注(0)|答案(2)|浏览(185)

下面给出了一个结果：{"a":null,"b":99.0,"c":null}我希望结果是{"b":99.0}，这样我就可以在JSON补丁中使用该结果。如何使用Sqlite/json1实现这一点？

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id INTEGER PRIMARY KEY, 
     a REAL, b REAL, c REAL
);

INSERT INTO test(a,b,c) 
VALUES (1,2,3), (1,99,3);

SELECT json_object(
           'a', NULLIF(new.a, curr.a), 
           'b', NULLIF(new.b, curr.b),
           'c', NULLIF(new.c, curr.c)
       ) AS result
  FROM test curr
 INNER JOIN test new ON curr.id
 WHERE new.id = 2 AND curr.id = new.id -1 ;

sqlite

来源：https://stackoverflow.com/questions/53209382/remove-k-v-pairs-with-value-null-from-json-object-in-query-result

2条答案

按热度按时间

zi8p0yeb1#

稍后会有一些小提琴：

SELECT json_group_object(key, value) 
  FROM json_each(json('{"a":null, "b":99.0, "c":null}')) AS result
 WHERE result.value IS NOT NULL;

结果为{"b":99.0}
所以整个事情就变成了这样：

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id INTEGER PRIMARY KEY, 
     a REAL, 
     b REAL, 
     c REAL
);

INSERT INTO test(a,b,c) 
VALUES (1,2,3), (1,99,3), (2,99,4), (1,999,3);

WITH J(kv) AS (
  SELECT json_object(
           'a', NULLIF(new.a, curr.a), 
           'b', NULLIF(new.b, curr.b),
           'c', NULLIF(new.c, curr.c)
         )
    FROM test curr
   INNER JOIN test new ON curr.id
   WHERE new.id = 2 AND curr.id = new.id -1
)
SELECT json_group_object(key, value) AS result 
  FROM json_each((SELECT kv FROM J)) AS kv
 WHERE kv.value IS NOT NULL;

赞(0）回复(0）举报 2022-11-14

zf9nrax12#

从json对象中删除Nulls的另一种方法是将其作为json补丁应用到空的json对象上：

select json_patch('{}','{"a":null,"b":99.0,"c":null}') ;

结果为{"b":99.0}

赞(0）回复(0）举报 2022-11-14

我来回答

sqlite 从查询结果中的json对象中删除值为‘NULL’的K，V对

2条答案

相关问题

热门标签

最新问答