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?
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;
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
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:
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})
;