Merge document with incremental changes

SQL++
, ,
1

Is there a way I can have a base json document(base) and small delta changes in different versions(derived) at different timestamp. Later when required I can merge this data and put together this information in one get query?

base
{
"type": "base",
id: 1,
"origin": "abc",
"destination": "xyz"
}
derived1
{
"type": "derived",
"parentDocId": 1,
"id": 1.1,
*"origin": "def",*
}
derived2
{
"type": "derived",
"parentDocId": 1,
"id": 1.2,
*"destination": "mno"*
}
derived3
{
"type": "derived",
"parentDocId": 1,
"id": 1.3,
*"destination": "qwe"*
}
derived3
{
"type": "derived",
"parentDocId": 1,
"id": 1.3,
*"origin": "poi"*
}
GET document should retrieve
{
"type": "base",
id: 1,
"origin": "**poi**",
"destination": "**qwe**"
}
2

Something like this may work if you have a list of known attributes for which you want to extract the latest version; it isn’t a simple “merge anything”.

CREATE INDEX ixt1 ON default(parentDocId);

SELECT d.id, d.type, orig, dest                                                                                                     
FROM default d
LET orig=(SELECT RAW origin FROM default d2 WHERE origin IS VALUED AND type = "derived" AND d2.parentDocId = d.id ORDER BY d2.id DESC)[0]
, dest=(SELECT RAW destination FROM default d3 WHERE destination IS VALUED AND type = "derived"  AND d3.parentDocId = d.id ORDER BY d3.id DESC)[0]
WHERE type = "base"
;

HTH.

3

Required 7.0+

CREATE INDEX ix1 ON default(type, id, parentDocId);

SELECT d AS basedoc,
       (SELECT p.*, MISSING AS type, MISSING AS id, MISSING AS parentDocId
        FROM default AS p
        WHERE p.type = "derived" AND p.parentDocId = d.id AND p.id IS NOT NULL
        ORDER BY p.id) AS revisions
FROM default AS d
WHERE d.type = "base" AND d.id IS NOT NULL AND d.parentDocId IS MISSING;

Issue second query by constructing based on results from first query.

SELECT OBJECT_CONCAT(basedoc, revisions[0], revisions[1], revisions[1], .....)

4

Another option, again with some hard limits (on the fields to be kept from the “base” document):

CREATE INDEX adv_type_id ON `default`(`type`,`id`);

SELECT object_concat(d1,
          object_remove(                                                                                                            
                  ( SELECT RAW object_concat(LEAD(d,1,{}) OVER (ORDER BY id),d)
                    FROM default d
                    WHERE parentDocId = d1.id
                    ORDER BY id desc
                         )[0]
                  ,"type"
                  ,"id"
                  ,"parentDocId"
                       )
                    )
FROM default d1
WHERE d1.type = "base";

HTH.