How to select an object within a collection?

If I understand your question, I have think I have a way of doing it by unnesting the array.

With the example document named “theDatabase”:

{
“id”: “1256”,
“theArray”: [
{
“field1”: “this”,
“field2”: “that”,
“field3”: “the”,
“field4”: “other”
},
{
“field1”: “tom”,
“field2”: “dick”,
“field3”: “harry”,
“field4”: “someoneelse”
}
]
}

I want to select the item with “someoneelse” as the value of field4.

SELECT theArray FROM default USE KEYS “theDatabase”
UNNEST theArray
WHERE theArray.field4=“someoneelse”

Yields the result:

{
“results”: [
{
“theArray”: {
“field1”: “tom”,
“field2”: “dick”,
“field3”: “harry”,
“field4”: “someoneelse”
}
}
]
}

This anything like what you are trying to do?

FIRST s IN sessions WHEN s.refresh_token = “2323232323” END

Hi @somexp ,

This works, but requires two lookups, the first to find the document id and the second to use that doc id, I was hoping to be able to do it in one…

thanks for the info though. :smiley: .

HI @geraldss ,

Could you expand on this a little, it looks interesting but i just keep getting syntax errors when i try all kinds of variations. Is it just me or as dp4 gone a bit backtick required crazy? :smiley:

Actually, what I got to work in the end was:

SELECT Meta(default).id, name, FIRST s FOR s  IN sessions WHEN s.refresh_token = "2323232323" END AS session FROM default WHERE type="user" AND (ANY token IN sessions SATISFIES token.refresh_token = "2323232323" END);

let me know if there is more of an efficient way but this does work. thanks for all the help and ideas…

Regards,

Paul.

That looks good.

-Gerald

Hi. I have a json document like below in a particular bucket.

{
“lineItemID”: 1,
“standards”: {
“other”: [“other1”, “other2” ],
“note”: [“note1”, “note2” ]
},
“language”: "Sample Language ",
“type”: “H”,
“metadata”: [ “metadata1”, “metadata2” ]
}

I want to select “note1” from the “standards” in this json. I have tried this using below query , but result is not coming.

SELECT
b.lineItemId,
b.metadata,
b.language,
b.type,
b.standards.note
FROM Bucketb WHERE
OBJECT_VALUES(standards).note LIKE “note1”;

Please help and provide me a query for this.

do you want this

SELECT b.lineItemID,
       b.metadata,
       b.language,
       b.type,
       b.standards.note
  FROM default b
 WHERE "note1" IN b.standards.note

Thanks for the query . Using this query I am getting data in below format:
{
“lineItemID”: 1,
“note”: [“note1”, “note2” ],
“language”: "Sample Language ",
“type”: “H”,
“metadata”: [ “metadata1”, “metadata2” ]
}

But I need the same in this format like the provided json:
{
“lineItemID”: 1,
“standards”: {
“note”: [“note1”, “note2” ]
},
“language”: "Sample Language ",
“type”: “H”,
“metadata”: [ “metadata1”, “metadata2” ]
}

FYI

SELECT b.lineItemID,
       b.metadata,
       b.language,
       b.type,
       OBJECT_ADD({},"note",b.standards.note) AS standards
  FROM default b
 WHERE "note1" IN b.standards.note

Thanks for the query.
But it is not working if more than one values are passed in standards like below.
SELECT b.lineItemID,
b.metadata,
b.language,
b.type,
OBJECT_ADD({},“note”,b.standards.note) AS standards
FROM default b
WHERE [“note1”,“notes2”,…] IN b.standards.note

Please suggest .

FYI

SELECT b.lineItemID,
       b.metadata,
       b.language,
       b.type,
       OBJECT_ADD({},"note",b.standards.note) AS standards
  FROM default b
 WHERE ANY n IN b.standards.note SATISFIES n IN ["note1","note2","note3"] END

Thanks a lot for you help. The query is working now.

Hi…
I have a new requirement for the above data like below:
{
“lineItemID”: 1,
“standards”: {
“other”: [
{id:“other1”,referenceUrl:""},
{id:“other2”,referenceUrl:""}
]
“note”: [
{id:“note1”,referenceUrl:""},
{id:“note2”,referenceUrl:""},
{id:“note3”,referenceUrl:""}
]
},
“language”: "Sample Language ",
“type”: “H”,
“metadata”: [ “metadata1”, “metadata2” ]
}

Now, need to select “note1”,“note3” from the “standards” in this json. I am using unnest clause to fetch.
Can you please help.

SELECT b.lineItemID,
       b.metadata,
       b.language,
       b.type,
       {"note": note} AS  standards
  FROM default b
 LET note = ARRAY v FOR v IN b.standards.note WHEN v.id IN  ["note1","note2"] END
 WHERE ARRAY_LENGTH(note) > 0;

It works. Thanks a lot… :slight_smile:

Hi…
I have two documents like below:
Document 1-
{
“eAstandards”: {
“asu201409”: [
{
“adoptableDate”: “12/01/2016”,
“entityType”: “PBE”,
“requiredDate”: “12/16/2017”
}
],
“asu201509”: [
{
“adoptableDate”: “12/01/2016”,
“entityType”: “PBE”,
“requiredDate”: “12/16/2017”
}
]
}
}

Document 2-
{
“lineItemID”: 5539,
“indent”: 2,
“earlyAdoptionTag”: “asu201509”,
“isDeleted”: false,
“smefinallanguage”: “Balance Sheet”,
“createdDate”: “10/12/2017”,
“createdBy”: “test user4”
}

Now, I want to display the data for lineItemID 5539 , like below where the details of earlyAdoptionTag will show like below:
O/P-
{
“lineItemID”: 5539,
“indent”: 2,
“earlyAdoptionTag”: “asu201509”,
“eAstandards”: {
[
{
“adoptableDate”: “12/01/2016”,
“entityType”: “PBE”,
“requiredDate”: “12/16/2017”
}
]
},
“isDeleted”: false,
“smefinallanguage”: “Balance Sheet”,
“createdDate”: “10/12/2017”,
“createdBy”: “test user4”
}

Please help me with the select query.

What are the Document Ids for Document 1 and Document 2?

The document ids are different for doc 1 & doc 2
suppose document 1 has id “1” and document 2 has id “2”

To Join in the N1QL the relation between documents needs to be present through the document key.
The following link explain full details how to JOIN in the N1QL https://dzone.com/articles/visually-explaining-n1ql-joins

i.e. doc1 needs to contain the doc2 document id or vice versa to join