Query array inside array

SQL++
1

Hi I have this document type

[
{
“item”: [
{
“categories”: [
“NAME”,
“CITY”,
“ADDRESS”,
“CAP”,
],
“specs”: [
“spec1”,
“spec2”
]
},
{
“categories”: [
“NAME”,
“CITY”,
“ADDRESS”,
],
“specs”: [
“spec1”
]
}
]
}
]

I want to extract the category that contains only spec1
and in another N1QL the category that contains spec1,spec2

I test this , but not work

SELECT Array item.categories FOR item IN item WHEN Array specs FOR specs IN item.specs When specs = ‘spec1’ END END AS item
FROM default

2 
INSERT INTO default VALUES("doc001",{
    "item": [
        {
            "categories": [
                "NAME",
                "CITY",
                "ADDRESS",
                "CAP"
            ],
            "specs": [
                "spec1",
                "spec2"
            ]
        },
        {
            "categories": [
                "NAME",
                "CITY",
                "ADDRESS"
            ],
            "specs": [
                "spec1"
            ]
        }
    ]
} );

SELECT i.categories
  FROM default a UNNEST a.item i
 WHERE "spec1" IN i.specs
   AND ARRAY_LENGTH(i.specs) == 1

SELECT i.categories
  FROM default a UNNEST a.item i
 WHERE "spec1" IN i.specs
   AND "spec2" IN i.specs
   AND ARRAY_LENGTH(i.specs) == 2
3

Thank you work perfect

4

Also try this.

SELECT i.categories
  FROM default a UNNEST a.item i
 WHERE ANY AND EVERY s IN i.specs SATISFIES s IN ["spec1","spec2"] END;

ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY

Range predicates (ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY) allow you to test a boolean condition over the elements or attributes of a collection or object(s). They each evaluate to a boolean value.

ANY or SOME is TRUE if the collection is non-empty and at least one element matches.

EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.

ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.

Same query can be used for both cases and change query parameter $spec to [“spec1”] or [“spec1”,“spec2”]

SELECT i.categories
  FROM default a UNNEST a.item i
 WHERE ANY AND EVERY s IN i.specs SATISFIES s IN $spec END;
1 Like