CODEWITHSUNDEEP
jsoncouchbasesql++
Pooja Nayak
Pooja Nayak

Reputation: 1

N1ql Queries for traversing array of objects

Below is couchbase document structure I am working on :

  {
"name":"Harry",
"lastname":"sam",
"supplier_info": {
    "HU": [
      {
        "40383": "Bangalore."
      },{
        "41163": "new."
        }
    ],
"SK": [
      {
        "40383": "DYNAMIT KFT."
      }
    ]
}

Requirement is to get all the documents from my db which has supplier number as "40383" irrespective of country. How can I do that with N1ql queries? (I do not have country info as well)

Upvotes: 0

Views: 425

Answers (1)

vsr
vsr

Reputation: 7414

SELECT * FROM default WHERE ANY v IN ARRAY_FLATTEN(OBJECT_VALUES(supplier_info),2) SATISFIES "40383" IN OBJECT_NAMES(v) END;

Input data

INSERT INTO default VALUES("kkk01",{ "supplier_info": { "HU": [ { "40383": "Bangalore." },{ "41163": "new." } ], "SK": [ { "40383": "DYNAMIT KFT." } ] }}), VALUES("kkk03",{ "supplier_info": { "HU": [ { "40383": "Bangalore." },{ "41164": "az." } ], "SK": [ { "40385": "DYNAMIT" } ] }});

The following query gives Distinct countries

SELECT DISTINCT country FROM (SELECT RAW ARRAY_FLATTEN(ARRAY ARRAY v.name FOR u IN v.val WHEN "40383" IN OBJECT_NAMES(u) END FOR v IN OBJECT_PAIRS(supplier_info) END,2) FROM default ) AS countries UNNEST countries AS country WHERE country IS NOT MISSING;

Getting supplier name, country, name and lastname for all the records with supplier number 40383.

SELECT q.name, q.lastname, s.country, s.snumber, s.sname FROM (SELECT name, lastname, ARRAY_FLATTEN(ARRAY ARRAY {"country":v.name, "snumber":OBJECT_PAIRS(u)[0].name, "sname":OBJECT_PAIRS(u)[0].val} FOR u IN v.val WHEN "40383" IN OBJECT_NAMES(u) END FOR v IN OBJECT_PAIRS(supplier_info) END,2) AS sinfo FROM default ) AS q UNNEST q.sinfo AS s;

Upvotes: 1

Related Questions