Wildcards in document path

SQL++
,
1

I have a bunch of documents structured like below and I want to construct a query to find all documents containing “areacode” = 999 kind of like

select * from mybucket where phone.*.areacode = “999”;

But this gives me Syntax Error. How would I construct such a query ?

{
“firstname” : “a”,
“lastname” : “b”,
“phone” : {
“home” : { “areacode” :“999”, “number”:“9990000”},
“mobile” : {“areacode”:“111”, “number”: “2223333”}
}
}

2

Any where (how nested deep) in phone that object contains arearcode field with “999” returns

SELECT *
FROM default
WHERE ANY p  WITHIN phone SATISFIES p.areacode = "999" END;

OR

Specific field only.

SELECT *
FROM default
WHERE ANY p  IN  OBJECT_PAIRS(phone) SATISFIES  p.name IN ["home", "mobile"] AND p.val.areacode = "999" END;
3

Thanks. That works.

I have another N1ql query question.

Now my document looks like below and I want to find all documents and corresponding orders that contain order item “paper”.
How would I do so ? Can I also return count of documents as part of the same query ?

{
 "firstname" : "a",
"lastname" : "b",
"phone" : {
"home" : { "areacode" :"999", "number":"9990000"},
"mobile" : {"areacode":"111", "number": "2223333"}
},
"orders" : {
         "order3" : {
              "_created" : "2020-01-09T16:50:41+00:00",
              "items" : [
                 "ink",
                 "paper",
                 "printer-paper"
              ]
          },
          "order4" : {
              "_created" : "2020-01-15T16:50:41+00:00",
              "items" : [
                 "pencils",
                 "paper",
                 "cartridges"
              ]
          }
    }
}


    {
    "firstname" : "kk",
    "lastname" : "jj",
    "phone" : {
    "home" : { "areacode" :"555", "number":"9991111"},
    "mobile" : {"areacode":"222", "number": "2220999"}
    },
    "orders" : {
             "order1" : {
                  "_created" : "2020-01-10T16:50:41+00:00",
                  "items" : [
                     "labels",
                     "paper",
                     "ink"
                  ]
              },
              "order2" : {
                  "_created" : "2020-01-16T16:50:41+00:00",
                  "items" : [
                     "pencils",
                     "binders",
                     "staples"
                  ]
              }
        }
    }
4 
SELECT op.name, COUNT(1) AS cnt, ARRAY_AGG(META(d).id) AS docs
FROM default AS d
UNNEST OBJECT_PAIRS(d.orders) AS op
WHERE ANY item IN op.val.items SATISFIES item = "paper" END
GROUP BY op.name;
5

That doesn’t exactly give me what I was looking for.
This is how I modified to get closer to what I am looking for except that now I get 3 documents.
I am looking to return 2 documents. One with 2 orders and a count of 2, and the other with one nested order and a count of 1.

SELECT d.firstname, d.lastname, op.val as oo
FROM test AS d
UNNEST OBJECT_PAIRS(d.orders) AS op
WHERE ANY item IN op.val.items SATISFIES item = “paper” END

6 
SELECT d.firstname, d.lastname, ARRAY o FOR o IN ov WHEN "paper" IN o.items  END orders , 
                  ARRAY_LENGTH(ARRAY o FOR o IN ov WHEN "paper" IN o.items  END ) AS cnt
FROM default AS d
LET ov = OBJECT_VALUES(d.orders) 
WHERE ANY o IN ov SATISFIES (ANY item IN o.items SATISFIES item = "paper" END) END;

If you are not looking for index

    SELECT d.firstname, d.lastname, av orders , ARRAY_LENGTH(av ) AS cnt
    FROM default AS d
    LET av =  ARRAY o FOR o IN  OBJECT_VALUES(d.orders)  WHEN "paper" IN o.items  END  
    WHERE  ARRAY_LENGTH(av) > 0;
7

Thanks again.

How about if my selection criteria is an array that is

  • o.items IN [‘cartridges’,‘pencils’]

OR

  • o.items NOT IN [‘cartridges’,‘pencils’]
8 
 SELECT d.firstname, d.lastname, av orders , ARRAY_LENGTH(av ) AS cnt
    FROM default AS d
    LET av =  ARRAY o FOR o IN  OBJECT_VALUES(d.orders)  WHEN (ANY v IN  ["cartridges","pencils"] SATISFIES v IN o.items  END  ) END
    WHERE  ARRAY_LENGTH(av) > 0;
9

That helps. I was able to get my NOT IN query also working as:

SELECT d.firstname, d.lastname, av orders , ARRAY_LENGTH(av ) AS cnt
FROM test AS d
LET av = ARRAY o
FOR o IN OBJECT_VALUES(d.orders)
WHEN (EVERY v IN [“cartridges”,“staples”] SATISFIES v NOT IN o.items END )
END
WHERE ARRAY_LENGTH(av) > 0;

My next challenge is to make it case-insensitive. However when I cast o.items to LOWER, that is, LOWER(o.items), I get no matches.
How can I do so ?

10

lower() can be done 0n strings not on ARRAYS ( construct new lower array and use ARRAY LOWER(v1) FOR v1 IN o.items END) .

SELECT d.firstname, d.lastname, av orders , ARRAY_LENGTH(av ) AS cnt
    FROM default AS d
    LET av =  ARRAY o FOR o IN  OBJECT_VALUES(d.orders)  WHEN (ANY v IN  o.items  SATISFIES LOWER(v) IN  ["cartridges","pencils"]  END  ) END
    WHERE  ARRAY_LENGTH(av) > 0;

also explore : ARRAY_LENGTH(ARRAY_INTERSECT([“cartridges”,“pencils”], o.items)) == 2

If you want use EVERY:

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.