N1ql Queries for traversing array of objects

Question

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)

Chintan Kotadiya · Accepted Answer · 2020-02-20 07:20:28Z

1

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;

edited Feb 20, 2020 at 7:20

Chintan Kotadiya

1,4251 gold badge12 silver badges19 bronze badges

answered May 23, 2017 at 14:41

vsr

7,4441 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pooja Nayak Over a year ago

What if I want to retrieve country(where "40383" exists and supplier name in the query instead of whole document???

vsr Over a year ago

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;

Pooja Nayak Over a year ago

Thanks a lot for the help :)

Pooja Nayak Over a year ago

My requirement is something as below: This is the whole object I have- { "name":"Harry", "lastname":"sam", "supplier_info": { "HU": [ { "40383": "Bangalore." },{ "41163": "new." } ], "SK": [ { "40383": "DYNAMIT KFT." } ] } I want a query to fetch supplier name, country, name and lastname for all the records with supplier number 40383. Thanks in advance.

Pooja Nayak Over a year ago

can you please help

|

Collectives™ on Stack Overflow

N1ql Queries for traversing array of objects

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related