Need Help - Break the query execution once it meets any one of its criteria

Hi -
From my custom logic, I got below result using a Sub Query. Now I need to pull one object as per this order 1. Zip 2. City 3. State 4. Nation. I want to return the result if the zip matches. If not, then I look for City and same logic till I find a matching object.

Note: I morphed my actual need, so please do not go by why Zip->City etc logic

[
{
“data”: “New York”,
“type”: “CITY”,
“value”: 456
},
{
“data”: “USA”,
“type”: “NATION”,
“value”: 656
},
{
“data”: “NY”,
“type”: “STATE”,
“value”: 556
},
{
“data”: “10007”,
“Type”: “ZIP”,
“value”: 256
}
]

    INSERT INTO default VALUES("k01",{"result":[ { "data": "New York", "type": "CITY", "value": 456 }, { "data": "USA", "type": "NATION", "value": 656 }, { "data": "NY", "type": "STATE", "value": 556 }, { "data": "10007", "type": "ZIP", "value": 256 } ]});
    INSERT INTO default VALUES("k04",{"result":[ { "data": "10007", "type": "ZIP", "value": 256 },{ "data": "New York", "type": "CITY", "value": 456 }, { "data": "USA", "type": "NATION", "value": 656 }, { "data": "NY", "type": "STATE", "value": 556 } ]});
    SELECT FIRST v FOR v IN nresult WHEN v.`value` = 456 END  AS doc  FROM default
    LET nresult = ARRAY (FIRST iv FOR iv IN result WHEN iv.type = tv END)  FOR tv IN ["ZIP","CITY","STATE","NATION"] END ;

Constructed new array nresult by arranging elements in the desired search order. Find the first matching element in the new array.

Hi vsr1 - Thanks for the reply. The above query is returning result only when city is on the top of the list.Let me restate my need clearly.

The order of objects is not constant(eg: State object may be on the top of the list) and I need to pull the “value” based on “Type” and “data”. While pulling I need to lookup in the below order, return the corresponding “value” whichever condition satisfies first.

Zip & it’s data --> City and it’s data -->State and it’s data --> Nation and it’s data.

Please let me know if you need more details.

Note: I got the above result using a Sub Query and am trying to get desired result in a single query.

Thanks in advance.

Please try the latest from the post (Also gave sample insert) and it works the way you described. If not working please post exact document query and output.

You can replace subquery in the following query.

SELECT FIRST v FOR v IN nresult WHEN v.`value` = 456 END  AS doc  FROM (  subquery ) AS result
LET nresult = ARRAY (FIRST iv FOR iv IN result WHEN iv.type = tv END)  FOR tv IN ["ZIP","CITY","STATE","NATION"] END ;

Hi VSR1: Thanks for your reply. I’m still not getting desired result. Please find the details below(Output, Query and Document) and I need to retrieve one “price” based on below query + location + locationType.

Below query returns 4 price objects with locationTypes zip, city, state and nation. Now, I need to pull only one price object as per this order(zip --> city --> state --> nation) whichever satisfies the condition first.

Note: 1. If locationType = ‘ZIP’ and location=‘10005’ I should return the result(Output1). No checks for city & state are required.
2. If zip doesn’t match, look for locationType = ‘CITY’ and location='New York 'and if city matches return the result (Output2)
3. If City doesn’t match look for State and return the result(Output3) if matches.

Output1: if Zip matches: [
{
“location”: “10005”,
“locationType”: “ZIP”,
“price”: 256
}
]
Output2: if City matches:
[
{
“location”: “New York”,
“locationType”: “CITY”,
“price”: 456
}
]
Output3: if State matches:
[
{
“location”: “NY”,
“locationType”: “STATE”,
“price”: 556
}
]

Query: select priceInfo.price, priceInfo.location, priceInfo.locationType
FROM default
UNNEST geo AS geo UNNEST geo.priceInfo AS priceInfo
where default.skuId = ‘sku1234’ and geo.startDate < NOW_STR() and geo.endDate > NOW_STR()
and priceInfo.tier=‘T1’

Document:
{
“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”
}
]
}
]
}

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  ;

Hi VSR1: Please execute this query with the above document I sent earlier. I always pass all below input parameters defined in the below query but need to pull the price object whichever satisfies first.

select priceInfo.price
FROM default
UNNEST geo AS geo UNNEST geo.priceInfo AS priceInfo
where default.skuId = ‘sku1234’ and geo.startDate < NOW_STR() and geo.endDate > NOW_STR()
and priceInfo.tier=‘T1’ and
(case when priceInfo.locationType = ‘ZIP’ AND priceInfo.location = ‘10005’ then priceInfo.price
when priceInfo.locationType = ‘CITY’ AND priceInfo.location = ‘New York’ then priceInfo.price
when priceInfo.locationType = ‘STATE’ AND priceInfo.location = ‘NY’ then priceInfo.price
when priceInfo.locationType = ‘NATION’ AND priceInfo.location = ‘USA’ then priceInfo.price
else 0
end);

It returns

[
{
“price”: 656
},
{
“price”: 256
},
{
“price”: 456
},
{
“price”: 556
}
]

But, it should return only below object as locationType = ‘ZIP’ AND location = ‘10005’ matches and should not check for other matches (city, state, nation)

{
“price”: 256
}

If zip’s location and locationType doesn’t match, it should look for locationType = ‘CITY’ AND location = ‘New York’ and so on.

Please let me know if you have any questions and suggest better query if my above approach is wrong.

SELECT  res
    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
    LET res = (ARRAY (FIRST iv FOR iv IN q.doc WHEN iv.locationType = tv.locationType AND iv.location = tv.location END)
              FOR tv IN [{"locationType":"ZIP", "location": "10005"},
                         {"locationType":"CITY", "location": "New York"},
                         {"locationType":"STATE", "location": "NY"},
                         {"locationType":"NATION", "location": "USA"}] END)[0]
    WHERE res IS NOT MISSING;

If nothing matched you want empty document remove WHERE res IS NOT MISSING

Input the values are in this format order is they way you want to search.

[{“locationType”:“ZIP”, “location”: “10005”},
{“locationType”:“CITY”, “location”: “New York”},
{“locationType”:“STATE”, “location”: “NY”},
{“locationType”:“NATION”, “location”: “USA”}]

OR

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 = 'ZIP' AND p.location = '10005') OR
                 (p.locationType = 'CITY' AND p.location = 'New York') OR
                 (p.locationType = 'STATE' AND p.location = 'NY') OR
                 (p.locationType = 'NATION' AND p.location = 'USA'))
           ORDER BY (CASE WHEN p.locationType = 'ZIP' THEN 0
                      WHEN p.locationType = 'CITY' THEN 1
                      WHEN p.locationType = 'STATE' THEN 2
                      WHEN p.locationType = 'NATION' THEN 3
                      ELSE 4 END)
            LIMIT 1;

Hi VSR1 - Both Solutions are working…Thank you. May I know which query gives better performance?

Both should be fine. Option 1 is more expandable based on requirements (If need data for all skuIds).

Than you vsr1. I need to run this query for multiple skus in single DB transaction for that I modified the query this way

Changes made in the query:WHERE d.skuId in [‘sku1234’,‘sku4567’] LIMIT 2

SELECT p.price, p.location, p.locationType
FROM default AS d
UNNEST geo AS geo
UNNEST geo.priceInfo AS p
WHERE d.skuId in [‘sku1234’,‘sku4567’] AND geo.startDate < NOW_STR() AND
geo.endDate > NOW_STR() AND p.tier=‘T1’ AND
((p.locationType = ‘ZIP’ AND p.location = ‘10005’) OR
(p.locationType = ‘CITY’ AND p.location = ‘New York’) OR
(p.locationType = ‘STATE’ AND p.location = ‘NY’) OR
(p.locationType = ‘NATION’ AND p.location = ‘USA’))
ORDER BY (CASE WHEN p.locationType = ‘ZIP’ THEN 0
WHEN p.locationType = ‘CITY’ THEN 1
WHEN p.locationType = ‘STATE’ THEN 2
WHEN p.locationType = ‘NATION’ THEN 3
ELSE 4 END)
LIMIT 2;

But, this query will not give correct result if for sku1234 has more than one matching Priceinfo object(eg: zip & state data matches) then it will return sku1234’s two priceInfo’s as limit is set to 2 and ignores sku4567 priceInfo object.

Would you please suggest me the solution?

Problem is not clear. Could you please state the problem clearly to handle when there is conflicted items. Example:
Given skuId locationType = ‘ZIP’ AND location = ‘10005’ produces more than 1 item which one do you want?
If there are different skuId’s do you want ignore skuId and produce match across skuId or do you need separate results for each skuId.

The following query produces matching entry for each skuId

SELECT d.skuId, MIN ([(CASE WHEN p.locationType = 'ZIP' THEN 0
                   WHEN p.locationType = 'CITY' THEN 1
                   WHEN p.locationType = 'STATE' THEN 2
                   WHEN p.locationType = 'NATION' THEN 3
                   ELSE 4 END),
            {p.price, p.location, p.locationType}])[1]
          FROM default AS d
          UNNEST geo AS geo
          UNNEST geo.priceInfo AS p
          WHERE d.skuId IN ['sku1234','sku4567'] AND geo.startDate < NOW_STR() AND
                geo.endDate > NOW_STR() AND p.tier='T1' AND
                ((p.locationType = 'ZIP' AND p.location = '10005') OR
                 (p.locationType = 'CITY' AND p.location = 'New York') OR
                 (p.locationType = 'STATE' AND p.location = 'NY') OR
                 (p.locationType = 'NATION' AND p.location = 'USA'))
          GROUP BY d.skuId;

This worked for my need. Thanks again for your time. Please find one more question below.

example document:

{
“doc_type”: “Donation”,
“donationId”: “1000121”,
“companyType”: [
“Big”,
“Medium”,
“Small”
],
“donationType”: [
[
“womenClothes”,
“menClothes”
],
[
“Cash”
]
]
}

I’ve 2 objects in donationType. It’s OR condition between the values of the same object(womenClothes or menClothes) and AND condition with other objects.
(eg: condition should match either womenClothes and Cash or menClothes and Cash ).

I’ve written a query to get donationId and companyType:

select d.donationId, d.companyType from default d
where doc_type = 'Donation’
AND ANY companyType IN d.companyType SATISFIES companyType IN [‘Medium’] END
AND ANY donationType IN d.donationType[0] SATISFIES donationType IN [‘womenClothes’,‘Cash’] END
AND ANY donationType IN d.donationType[1] SATISFIES donationType IN [‘womenClothes’,‘Cash’] END

I got this result:

[
{
“companyType”: [
“Big”,
“Medium”,
“Small”
],
“donationId”: “1000121”
}
]

My Questions: 1. How shall I get only matching companyType in the output instead of complete list(‘Medium’ in this case)
2. Is my donationType condition correct in the where clause? How to handle if more Objects added at later point of time
eg: “donationType”: [
[
“womenClothes”,
“menClothes”
],
[
“Cash”
],
[
“furniture”
]
]
Please Suggest.

SELECT d.donationId, companyType
FROM default AS d
UNNEST d.companyType AS companyType
WHERE d.doc_type = 'Donation' AND
      companyType = 'Medium' AND
      ANY v IN [['womenClothes','Cash'], ['menClothes','Cash']]  SATISFIES
           (ANY AND EVERY iv IN v SATISFIES
                    (ANY dv WITHIN d.donationType SATISFIES dv = iv END)
           END)
      END;

FYI: To benefit others start a new post for unrelated questions.