How to replace ids in array with objects

SQL++
1

Given db with objects like:

[
  {
    _id: "TI::1233",
    "_class": "com.model.Transaction",
    "linkedLists": [
      "LI::f78067af-272d-4048-a2d1-b5358e14d70d"
    ]
  },
  {
    _id: "LI::f78067af-272d-4048-a2d1-b5358e14d70d",
    "_class": "com.model.ListItem",
  },
]

Question: How to make a query to receive result like this:

[
 {
    _id: "TI::1233",
    "_class": "com.model.Transaction",
    "linkedLists": [
      {
        _id: "LI::f78067af-272d-4048-a2d1-b5358e14d70d",
        "_class": "com.model.ListItem",
      },
    ]
  },
]

Thanks in advance!

2

@Vladlan ,

Reconstruct array

ARRAY {"_id": v, "_class": "com.model.ListItem"} FOR v IN linkedLists END

If you are looking to change the document.

UPDATE mybucket AS b
SET b.linkedLists = ARRAY  {"_id": v,  "_class": "com.model.ListItem"} FOR v IN b.linkedLists END
WHERE ......

If query


SELECT b.*, 
       ARRAY {"_id": v, "_class": "com.model.ListItem"} FOR v IN b.linkedLists END AS linkedLists
FROM mybucket AS b
WHERE .....;

OR

SELECT b.*, 
       (SELECT ll AS _id, "com.model.ListItem" AS _class FROM b.linkedLists AS ll) AS linkedLists
FROM mybucket AS b
WHERE .....;
3

@vsr1 , thanks for help, but what if objects with _class: "com.model.ListItem has more keys with different values, like

{
    _id: "LI::f78067af-272d-4048-a2d1-b5358e14d70d",
    _class: "com.model.ListItem",
   listType: "event",
   name: "some name",
   timestamp: "2021-03-31T16:16:34.351Z",
   ....(and others)
  },

?

Thanks in advance

4

Not sure how it possible (If linkedLists as array of scalars ).
If it is already object and want to add new field class then use

ARRAY OBJECT_ADD(v,  "_class", "com.model.ListItem") FOR v IN b.linkedLists END AS linkedLists

OR

(SELECT ll.*, "com.model.ListItem" AS _class FROM b.linkedLists AS ll) AS linkedLists

If you want add constant object
pass $newobj as named parameter or replace with constant

{
    "_class": "com.model.ListItem",
   "listType": "event",
   "name": "some name",
   "timestamp": "2021-03-31T16:16:34.351Z",
   ....(and others)
  }

SELECT b.*,
       ARRAY OBJECT_CONCAT({"_id": v}, $newobj) FOR v IN b.linkedLists END AS linkedLists
FROM mybucket AS b
WHERE .....;

OR

SELECT b.*,
       (SELECT ll AS _id, $newobj.* FROM b.linkedLists AS ll) AS linkedLists
FROM mybucket AS b
WHERE .....;