How can i use Let and Subquery result to update or insert Data

SQL++
1

I have a query where user can create a new item in the lookup table and i want to set the sort order to the current max in array + 1

To get that nbr I want to use the following query

SELECT Raw MAX(d.sort_order) + 1 AS SortCount
    FROM Contacts c USE KEYS 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
    UNNEST c.data d

Which returns me this

[
  5
]

But when i do a

select MaxSelectCount
 let MaxSelectCount = (SELECT Raw MAX(d.sort_order) + 1 AS SortCount
    FROM Contacts c USE KEYS 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
  UNNEST c.data d)

i get this

[
  {
    "MaxSelectCount": [
      5
    ]
  }
]

What i am looking for is a way to use the max nbr + 1 to update an Update Query

UPDATE Contacts c USE KEYS 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
SET data = ARRAY_APPEND(data,{ "id": 'rfrf', "name": 'rfrfrfrf', "sort_order": SortMaxCount }
)

let SortMaxCount = (SELECT Raw MAX(d.sort_order) + 1 AS SortCount
    FROM Contacts c USE KEYS 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
    UNNEST c.data d)
2

You will not able to use LET in UPDATE statement.
With out subquery you can achieve this as follows. If you need subquery in-line and take subscript of [0]

UPDATE Contacts c USE KEYS 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
SET c.data = ARRAY_APPEND(c.data,{ "id": 'rfrf', "name": 'rfrfrfrf', 
                                 "sort_order": IFMISSINGORNULL(ARRAY_MAX(c.data[*].sort_order),0)+1 });

OR

UPDATE Contacts c USE KEYS 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
SET data = ARRAY_APPEND(data,{ "id": 'rfrf', "name": 'rfrfrfrf',
                               "sort_order": (SELECT Raw MAX(d.sort_order) + 1 AS SortCount
                                              FROM Contacts c USE KEYS 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
                                              UNNEST c.data d)[0]

                   );
3

Ok, either will work but here is a different question, as i build the data to insert in my SDK i am wondering if i can pass the query and then make sure its handled as a query not as string.

when i pass it as string the couchbase n1ql handles as string

  key: 'lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F',
    item: Lookup {
      id: '3eefde',
      name: 'efe',
      sort_order: 'IFMISSINGORNUL(ARRAY_MAX(c.data[*].sort_order),0)+1'
    }
4

It can’t be string. IFMISSINGORNUL(ARRAY_MAX(c.data[*].sort_order),0)+1 must be expression as part of SQL statement

UPDATE Contacts c USE KEYS  $key
SET c.data = ARRAY_APPEND(c.data, 
                          OBJECT_PUT($item,  "sort_order", IFMISSINGORNULL(ARRAY_MAX(c.data[*].sort_order),0)+1 ));

using above SQL statement pass named parameters

$key='lookup::D6D80816-EE16-4C73-BDAA-D171B46BCC4F'
$item= { "id": '3eefde', "name": 'efe'}

takes $item object adds “sort_order” field and value as expression calculated from document and append to data