Conditional SELECT return according to a nested value

SQL++
,
1

Hello,

I have a list of documents related with reports of validations applied to items, on this case I need to select the reports that include a certain product “p00”, currently this WHERE condition is working fine.

The issue happens when returning a productStatus depending if the product has an item wiith a validation error with a certain severity: Severity > 1 should return “Errors”, Severity > 0 should return “Warnings” and ELSE condition Success.

This is my document :

{
	id: "1",
	description: "Report of error of channel X",
	overallStatus: "Error",
	okItems: [
		{
			item: {
				id: "p00-item01"
				product: {
					id: "p00"
				}
			},
		}
	]
	validationErrors: [
		{
			item: {
				id: "p00-item00"
				product: {
					id: "p00"
				}
			},
			errors: [
				{
					severity: 2,
					description: "Some validation error"
				},
				{
					severity: 2,
					description: "Some other validation error"
				},
				{
					severity: 1,
					description: "Minor validation error"
				},
				{
					severity: 1,
					description: "Other type minor validation error"
				},
			]
		},
		{
			item: {
				id: "p05-item01"
				product: {
					id: "p05"
				}
			},
			errors: [
				{
					severity: 1,
					description: "Some validation error"
				},
				{
					severity: 1,
					description: "Some other validation error"
				},
			]
		}
	]
}

Please take a look on my query:

SELECT
  meta(er).id as id, description,
  CASE 
   WHEN (
     ANY validationError IN er.validationErrors SATISFIES 
       validationError.item.product.id = 'p00' AND
       (ANY error IN errors SATISFIES
         error.severity > 1 END)
     END) THEN  "Error"
   WHEN (
     ANY validationError IN er.validationErrors SATISFIES
	   validationError.item.product.id = 'p00' AND
	   (ANY error IN errors SATISFIES
         error.severity > 0 END)
     END) THEN "Warnings"
   ELSE
     "Successs"
  END AS ProductStatus
FROM `Lucky_Bucket` AS er
WHERE 
    er.type = 'exportr'
  AND
    ( 
      ANY validationError IN er.validationErrors SATISFIES
        validationError.item.product.id = 'p00'
      END
    )

The desired result should be on this case:

[
  {
    id: "1",
    description: "Report of error of channel X",
    productStatus: "Error"
 }
]

On this case the couchbase version is 6.6

2

@IRobot ,

Qualify the errors in the case with validationError otherwise it takes from root of the document. (To avoid unexpected result best practice qualify the fields. Only JOINs complain, non-join query always qualify with keyspace/bucket. If need to use from WITH/LET/binding variable you need to qualify with that).

SELECT meta(er).id as id, er.description,
  CASE WHEN (ANY ve IN er.validationErrors SATISFIES AND (ANY vee IN ve.errors SATISFIES vee.severity > 1 END) END) THEN "Error"
       WHEN (ANY ve IN er.validationErrors SATISFIES AND (ANY vee IN ve.errors SATISFIES vee.severity > 0 END) END) THEN "Warnings"
       ELSE "Successs" END AS ProductStatus
FROM `Lucky_Bucket` AS er
WHERE er.type = 'exportr'
      AND ANY ve IN er.validationErrors SATISFIES ve.item.product.id = 'p00' END