Embeded results {'bk':bk, { 'cC':cC} as cC } as bk

#1

Hi Team,
Is this possible? I get “code”: 3000, “msg”: “syntax error - at {”

select p.*, { ‘bk’:bk, { ‘cC’:cC } as cC } as bk, { ‘sy’:sy } as sy
from NAPS p
JOIN NAPS bk ON KEYS ‘bk_’ || TOSTRING(p.bkId)
JOIN NAPS cC ON KEYS ‘cC_’ || TOSTRING(bk.cCId)
JOIN NAPS sy ON KEYS ‘sy_’ || TOSTRING(p.iId)
WHERE sy.CCY == 'EUR’
AND bk.entityType == 'bk’
AND sy.entityType == 'sy’
AND cC.entityType == 'cC’
AND p.entityType == ‘p’ ;

Basically, bk contains sub-object cC…if we wanted to transform the JSON results back to an object, it has to be embedded in result.

Best,
Peter

#2

Hello,

Please try the following:

#3

Works thanks. Is there way to aggregate Array values across documents so if
id:doc_1, array_1 : [ 1, 2,3]
id:doc_2, array_1 : [ 1,2,3]
can generate summary array_1 : [ 2,4,6 ]

Best

#4

Do you know in advance the length of the arrays?

#5

Yes, the array will be of length = 4

#6

SELECT [ SUM(a[0]), SUM(a[1]), SUM(a[2]), SUM(a[3]) ]
FROM my_bucket;

#7

Excellent, that works thank you