What is input of this query just location or location and locationType.
Example: Get the output2 are you giving “locationType”: “CITY” and “location”: “New York” OR
just “location”: “New York”, in that case look locationType in the order “ZIP”,“CITY”,“STATE”,“NATION” and when location matched return that one.
INSERT INTO default VALUES("k01",{ "doc_type": "sku", "skuId": "sku1234", "productId": "prod1234", "geo": [ { "version": 0, "startDate": "2011-01-10T00:00:00", "endDate": "2016-07-31T22:00:00", "priceInfo": [ { "id": "pr1000003", "price": 135, "tier": "T1", "locationType": "STATE", "location": "NY", "startDate": "2014-02-28T00:00:00", "endDate": "2016-05-24T00:00:00" }, { "id": "pr1000004", "price": 171, "tier": "T2", "locationType": "ZIP", "location": "10005", "startDate": "2013-02-28T00:00:00", "endDate": "2018-05-24T00:00:00" } ] }, { "version": 1, "startDate": "2016-01-01T00:00:00", "endDate": "2020-12-31T00:00:00", "priceInfo": [ { "id": "pr10000121", "price": 656, "tier": "T1", "locationType": "NATION", "location": "USA", "startDate": "2014-02-28T00:00:00", "endDate": "2018-12-24T00:00:00" }, { "id": "pr10000121", "price": 256, "tier": "T1", "locationType": "ZIP", "location": "10005", "startDate": "2014-02-28T00:00:00", "endDate": "2018-12-24T00:00:00" }, { "id": "pr10000121", "price": 456, "tier": "T1", "locationType": "CITY", "location": "New York", "startDate": "2014-02-28T00:00:00", "endDate": "2018-12-24T00:00:00" }, { "id": "pr10000121", "price": 556, "tier": "T1", "locationType": "STATE", "location": "NY", "startDate": "2014-02-28T00:00:00", "endDate": "2018-12-24T00:00:00" }, { "id": "pr20000121", "price": 256, "tier": "T2", "locationType": "ZIP", "location": "10021", "startDate": "2014-02-28T00:00:00", "endDate": "2018-12-24T00:00:00" }, { "id": "pr20000121", "price": 456, "tier": "T2", "locationType": "CITY", "location": "New Jersy", "startDate": "2014-02-28T00:00:00", "endDate": "2018-12-24T00:00:00" } ] } ] });
Input is locationType AND location
\SET -$locationType "CITY";
\SET -$location "New York";
SELECT p.price, p.location, p.locationType
FROM default AS d
UNNEST geo AS geo
UNNEST geo.priceInfo AS p
WHERE d.skuId = 'sku1234' AND geo.startDate < NOW_STR() AND geo.endDate > NOW_STR() AND p.tier='T1'
AND p.locationType = $locationType AND p.location = $location;
Input is just location and look for first matching of all location Type in this order "ZIP","CITY","STATE","NATION"
\SET -$location "USA";
SELECT FIRST o FOR o IN ( ARRAY (FIRST iv FOR iv IN (SELECT p.price, p.location, p.locationType
FROM default AS d
UNNEST geo AS geo
UNNEST geo.priceInfo AS p
WHERE d.skuId = 'sku1234' AND geo.startDate < NOW_STR() AND
geo.endDate > NOW_STR() AND p.tier='T1')
WHEN iv.locationType = tv END) FOR tv IN ["ZIP","CITY","STATE","NATION"] END)
WHEN o.location = $location END AS doc ;
SELECT FIRST o FOR o IN ( ARRAY (FIRST iv FOR iv IN q.doc
WHEN iv.locationType = tv END) FOR tv IN ["ZIP","CITY","STATE","NATION"] END)
WHEN o.location = $location END AS doc
FROM (SELECT ARRAY_AGG({p.price, p.location, p.locationType}) AS doc
FROM default AS d
UNNEST geo AS geo
UNNEST geo.priceInfo AS p
WHERE d.skuId = 'sku1234' AND geo.startDate < NOW_STR() AND
geo.endDate > NOW_STR() AND p.tier='T1') AS q ;
If bucket has multiple documents and want to keep each subquery results separately
SELECT FIRST o FOR o IN ( ARRAY (FIRST iv FOR iv IN q.doc
WHEN iv.locationType = tv END) FOR tv IN ["ZIP","CITY","STATE","NATION"] END)
WHEN o.location = $location END AS doc
FROM (SELECT ARRAY_AGG({p.price, p.location, p.locationType, META(d).id}) AS doc
FROM default AS d
UNNEST geo AS geo
UNNEST geo.priceInfo AS p
WHERE d.skuId = 'sku1234' AND geo.startDate < NOW_STR() AND
geo.endDate > NOW_STR() AND p.tier='T1'
GROUP BY META(d).id ) AS q ;