Wsrtjtyk

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 ;

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;