How to select an object within a collection?

shopapps · February 12, 2015, 5:50pm

Hi,

if i have a document that looks like this:

"sessions": [
    {
      "access_token": "1234567",
      "expires_in": 60,
      "token_type": "Bearer",
      "refresh_token": "246810",
      "expires": 1424366376175
    },
    {
      "access_token": "11111111111111",
      "expires_in": 60,
      "token_type": "Bearer",
      "refresh_token": "1212121212",
      "expires": 1424366376175
    },
    {
      "access_token": "22222222222222",
      "expires_in": 60,
      "token_type": "Bearer",
      "refresh_token": "2323232323",
      "expires": 1424366376175
    }]

Is there a simple way to select an entire token object within the sessions array?

e.g. if i had a variable value for refesh_token of 2323232323 I would ike to get back:

{
 "access_token": "22222222222222",
 "expires_in": 60,
"token_type": "Bearer",
"refresh_token": "2323232323",
 "expires": 1424366376175
}

I have figured out a lookup to find the document and pick some bits out of it e.g.:

SELECT Meta(default).id, `user`.`email`, `user`.`name` FROM default WHERE type="user" AND (ANY token IN sessions SATISFIES token.refresh_token = "2323232323" END);

but cant see how to select the whole object the given refresh_token is located inside?

Hopefuly that makes sense?

Thanks.

Paul.

somexp · February 12, 2015, 6:48pm

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?

geraldss · February 12, 2015, 6:49pm

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

shopapps · February 13, 2015, 9:19am

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. .

shopapps · February 13, 2015, 9:28am

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?

shopapps · February 13, 2015, 9:51am

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.

geraldss · February 13, 2015, 2:32pm

That looks good.

-Gerald

moumita.b.ghosh · September 13, 2017, 7:41am

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.

atom_yang · September 13, 2017, 11:47am

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

moumita.b.ghosh · September 13, 2017, 12:44pm

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” ]
}

atom_yang · September 13, 2017, 1:07pm

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

moumita.b.ghosh · September 14, 2017, 7:18am

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 .

atom_yang · September 14, 2017, 10:42am

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

moumita.b.ghosh · September 14, 2017, 11:12am

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

moumita.b.ghosh · September 19, 2017, 2:15pm

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.

vsr1 · September 19, 2017, 4:24pm

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;

moumita.b.ghosh · September 21, 2017, 8:53am

It works. Thanks a lot…

moumita.b.ghosh · October 15, 2017, 8:39am

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.

vsr1 · October 15, 2017, 1:35pm

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

moumita.b.ghosh · October 15, 2017, 3:12pm

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

Topic		Replies	Views
Select object containing value nearest to the specified value from an array SQL++ query , n1ql	4	1007	January 29, 2018
How can i get an object in array stored in doc SQL++	6	622	April 23, 2021
Selecting from subquery SQL++	1	619	December 13, 2018
Is it possible to use array indexing and querying with object collections/dictionaries, not arrays? SQL++ query , n1ql	6	1584	December 7, 2017
Query to select nested object inside an array SQL++ n1ql	1	7302	August 4, 2017

How to select an object within a collection?

Related topics