Creating an index on a nested fields with a dynamic parent name

Couchbase Server
1

I’m trying to create an index on the nested putCall field, however the 1000.0 parent and the 2019-02-01:22 grandparent are dynamically generated names and they are arrays. The first child of 2019-02-01:22 is 1000.0 but it can have many children with different names (i.e. 900.0, 800.0, …) each child will contain a putCall element. How can I create an index without using the parent names?

{
“symbol”: “GOOG”,
“underlyingPrice”: 1069.945,
“callExpDateMap”: {
“2019-02-01:22”: {
“1000.0”: [
{
“putCall”: “CALL”,
“bid”: 75,
“ask”: 83,
“delta”: 0.838,
}
]
}
}
}

Thanks

2 
CREATE INDEX ix1 ON default ( DISTINCT ARRAY (DISTINCT ARRAY v2.putCall FOR v2 IN ARRAY_FLATTEN(OBJECT_VALUES(v1),2) END) FOR v1 IN OBJECT_VALUES(callExpDateMap) END);

SELECT d.* FROM default AS d
WHERE ANY v1 IN OBJECT_VALUES(d.callExpDateMap) 
                    SATISFIES (ANY v2 IN ARRAY_FLATTEN(OBJECT_VALUES(v1),2) 
                                              SATISFIES  v2.putCall = "CALL" END) END;
3

I will give this a try. I’m not exactly clear what’s going on in your Create Index statement so I’ll need to check out the Index documentation.

Thanks

4

As object has dynamic field and without knowing field name no other way access nested object.
We used OBJECT_VALUES(v) which converts object into array by each field value into element in array. Then we used Array indexing.
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/objectfun.html
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/indexing-arrays.html

5

I’ve been trying to SELECT only the elements in the nested array with putCall = “CALL” but I’m getting back everything equivalent to entering:
SELECT d.* FROM OptionChain AS d

Given the following document:
{
“symbol”: “GOOG”,
“underlyingPrice”: 1069.945,
“callExpDateMap”: {
“2019-02-01:22 ”: {
“ 1000.0 ”: [
{
“putCall”: “CALL”,
“bid”: 75,
“ask”: 83,
“delta”: 0.838,
}
]
“ 1000.0 ”: [
{
“putCall”: “PUT”,
“bid”: 75,
“ask”: 83,
“delta”: 0.838,
}
]
}
}
}

I’m trying to generate a query (and a corresponding index to improve query time) that will return back the above document with the PUT array elements removed. So I should only see:

{
“symbol”: “GOOG”,
“underlyingPrice”: 1069.945,
“callExpDateMap”: {
“2019-02-01:22 ”: {
“ 1000.0 ”: [
{
“putCall”: “CALL”,
“bid”: 75,
“ask”: 83,
“delta”: 0.838,
}
]
}
}
}

Thanks

6

With out knowing dynamic fields selecting them it will be complex. On top you what represent the way you want makes difficult.

INSERT INTO default VALUES("kk01", { "symbol": "GOOG", "underlyingPrice": 1069.945, "callExpDateMap": { "2018-02-01:22 ": { " 1000.0 ": [ { "putCall": "CALL", "bid": 75, "ask": 83, "delta": 0.838 }, { "putCall": "PUT", "bid": 75, "ask": 83, "delta": 0.838 } ] }, "2019-02-01:22 ": { " 1000.0 ": [ { "putCall": "CALL1", "bid": 75, "ask": 83, "delta": 0.838 }, { "putCall": "PUT", "bid": 75, "ask": 83, "delta": 0.838 } ] } } });
SELECT t1.d.*, OBJECT v1.name:v1.v FOR v1 IN a END AS callExpDateMap FROM (
SELECT t.d, t.name, OBJECT v1.vn:v1.v FOR v1 IN a END AS v
FROM ( SELECT d, d1.name, d2.name AS vn, a AS v
         FROM default AS d
         UNNEST OBJECT_PAIRS(d.callExpDateMap) AS d1
         UNNEST OBJECT_PAIRS(d1.val) AS d2
         UNNEST d2.val AS d3
         WHERE d3.putCall = "CALL"
         GROUP BY d, d1.name, d2.name
         LETTING a = ARRAY_AGG(d3)) AS t
GROUP BY t.d, t.name
LETTING a = ARRAY_AGG({t.vn, t.v})) AS t1
GROUP BY t1.d
LETTING a = ARRAY_AGG({t1.name, t1.v})
;