n1ql help to get documents that meet params

SQL++
1

I have document with these fields:
{
“id”: “1-o-32”,
“retailer”: “1067”,
“supplier”: “3533”,
“messages”: null,
“type”: “order”,
“dateValues”: [
{
“key”: “PO Date”,
“value”: “11/06/2015”
},
{
“key”: “Ship Date”,
“value”: “11/10/2015”
},
{
“key”: “Cancel Date”,
“value”: “11/08/2015”
},
{
“key”: “Actual Date”,
“value”: “”
},
{
“key”: “Close Date”,
“value”: “”
}
]
}

i want to get all document that meet my params but it is not returning a result. my query below

SELECT * FROM order WHERE type = ‘order’ AND retailer=‘1067’ AND supplier =‘3533’ AND dateValues.Key =‘PO Date’ AND dateValues.Value = ‘11/06/2015’

2

Try the following:

SELECT *
FROM order
WHERE type = ‘order’ AND retailer=‘1067’ AND supplier =‘3533’ AND
ANY dv IN dateValues SATISFIES dv.Key =‘PO Date’ AND dv.Value = ‘11/06/2015’ END;

3

@geraldss that did not return a result a result. i have a primary index do i need a secondary one also.

4

It is case sensitive. Replace Key and Value with key and value.

Sent from my T-Mobile 4G LTE Device

5

@geraldss i tried that and same result nothing return when i remove the ANY part from the query it returns the list of results

6

Can you copy and paste 2 things:

  • the exact document in your dataset
  • the exact query you are running

Please copy and paste, so that it is exact.

7

@geraldss here is the dataset and the query i am working with

{
“orderPKey”: “32”,
“retailerPKey”: “1067”,
“supplierPKey”: “3533”,
“clientPKey”: “1”,
“poNumber”: “54646464”,
“invoiceStatus”: null,
“commissionStatus”: null,
“dateValues”: [
{
“key”: “PO Date”,
“value”: “11/06/2015”
},
{
“key”: “Ship Date”,
“value”: “11/10/2015”
},
{
“key”: “Cancel Date”,
“value”: “11/08/2015”
},
{
“key”: “Actual Date”,
“value”: “”
},
{
“key”: “Close Date”,
“value”: “”
}
],
“messages”: null,
“type”: “order”,
“id”: “1-o-32”
}

SELECT order.* FROM order WHERE type = ‘order’ AND clientPKey =‘1’ AND retailerPKey =‘1067’ AND supplierPKey =‘3533’ AND ANY dv IN dateValues SATISFIES dv.key =‘PO Date’ AND dv.value = ‘11/06/2015’ END;