sqlitesqlite-json1

Remove K,V pairs with value 'NULL' from json object in query result


The following gives me a result : {"a":null,"b":99.0,"c":null} I'd like to have {"b":99.0} as a result so I can use the result in a JSON patch. How can I achieve this with 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 ;

Solution

  • Some fiddling later:

    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;
    

    results in {"b":99.0}

    So the whole thing becomes something like:

    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;