How to add new field to each element of array which is inside another array?

SQL++
1

Hello everyone,

I’m new at Couchbase (4.5) and I got this structure:

{ 
   items: [
    {
      id: "a1",
      subitems: [
        {
          id: "b1",
          name: "joe1"
        },
        {
          id: "b2",
          name: "joe2"
        }
      ] 
    }
  ],
  docType: "mydoc"   
}

I need to add new field to each object inside subitems. I know how to do this with 1 level array =>

UPDATE default SET a.newField='blabla' FOR a IN items END where docType = 'mydoc';

It will produce that:

{ 
  items: [
    {
      newField: "blabla",
      id: "a1",
      subitems: [
        {
          id: "b1",
          name: "joe1"
        },
        {
          id: "b2",
          name: "joe2"
        }
      ] 
    }
  ],
  docType: "mydoc"   
}

But I need this result:

{ 
  items: [
    {
      id: "a1",
      subitems: [
        {
          newField: "blabla",
          id: "b1",
          name: "joe1"
        },
        {
          newField: "blabla",
          id: "b2",
          name: "joe2"
        }
      ] 
    }
  ],
  docType: "mydoc"   
}

Thanks for any help.

2

Hi,

I added a third syntax option on Stack Overflow: http://stackoverflow.com/questions/37538047/n1ql-how-to-add-new-field-to-each-element-of-array-which-is-inside-another-arr/37546122#37546122

3

Hi,

I don’t see any relation to subitems array in third query, are you sure?

4

You should try it out.

5

Thank you, third works!

1 Like
6

Works but if “items” array contains other json objects it will add newField there also. It’s incorrect. In other words third query adds newField to every json object inside “items” array. I need to add newField only to elements of “subitems” array.

7

You are right. The following will only update subitems.

UPDATE default
SET i.subitems = ( ARRAY OBJECT_ADD(s, 'newField', 'blabla' ) FOR s IN i.subitems END ) FOR i IN items END;
8

Hi…
In a bucket , I have multiple documents like below .
{
“lineItemID”: 1,
“standards”: {
“other”: [“other1”, “other2” ],
“note”: [“note1”, “note2” ]
},
“language”: "Sample Language ",
“type”: “H”,
“metadata”: [ “metadata1”, “metadata2” ]
}

I want to add a new field “lineItemHeader” for all document with in that bucket .
Could you please help .

9 
UPDATE default
SET lineItemHeader = "....." WHERE type = "H";

If this not what you are looking post the expected output.

10

The expected output after adding a new field for all document like below:

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

can this possible using the above UPDATE statement.

11

It is possible.

INSERT INTO default VALUES("k01", { "lineItemID": 1, "standards": { "other": ["other1", "other2" ], "note": ["note1", "note2" ] }, "language": "Sample Language ", "type": "H", "metadata": [ "metadata1", "metadata2" ] });
UPDATE default SET lineItemHeader = "H";
12

Thanks a lot for your help. It works.

13

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.

14

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