How to add multiple elements to an array in a query

SQL++
1

Hi,

I am struggling with adding multiple elements to an array as part of a query. Here is an example of my documents:

{
"productid": "1",
"ingredients":[{"ingid": "1"},{"ingid": "2"}]
"type":"product"
}
{
"ingid":"1",
"name":"sugar",
"calories": 4,
"type":"ingredients"
}
{
"ingid":"2",
"name":"flour",
"calories": 6,
"type":"ingredients"
}

and I want to return this:

{
"productid":"1",
"ingredients":[
  {"ingid":"1", "name":"sugar", "calories": 4}.
  {"ingid":"2", "name":"flour", "calories": 6}
  ]
}

I am aware of the ARRAY_AGG function which combined with OBJECT_ADD allows me to add a single element, but how can I add both the name and the calories elements to each entry? I have tried using ARRAY_CONCAT and ARRAY_APPEND, but I don’t seem to be able to make them work.

Thanks a lot,
Bertrand.

2 
SELECT p. productid, ARRAY_AGG({a.ingid, a.name, a.calories}) AS ingredients
FROM mybucket AS p
LEFT UNNEST p.ingredients AS i
LEFT JOIN mybucket AS a ON i.ingid =  a.ingid AND a.type = "ingredients"
WHERE p.type = "product"
GROUP BY p. productid

https://index-advisor.couchbase.com/indexadvisor/#1

3

Thank you for the quick response. This helps me understand how to create the objects I want. This is awesome. Thank you!!!

4

@Bertrand_Sirodot , You can construct Objects from expression as filed name colon expression, nested object etc

{“fname”: lower(“xyz”), “address”: {“street”: “Time square”}}

5

Hi,

your response was awesome and allowed me to get one step closer to what I want the query to achieve, but now I am running into another issue and can’t find a way around it, but I am sure there is one. Here is the structure of the document:

{
  "boats": [
    {
      "boatid": "boat::L4tdDXPxyTyDrqQJRMWTUP",
      "captainid": "user::p4zMjxu6DvdRT3NZxyKm2E",
      "crews": [
        {
          "checkedin": false,
          "email": "member-HmESvYQf@foo.com",
          "name": "member-HmESvYQf",
          "userid": "user::E9QJDzktNTSnTYm9W5aSrg"
        },
        {
          "checkedin": false,
          "email": "member-mwTXtIBc@foo.com",
          "name": "member-mwTXtIBc",
          "userid": "user::a5AczU4r22zWvhQcndSdhZ"
        }
      ],
      "sail": "fvYGZ637"
    },
    {
      "boatid": "boat::cBwGp93NCPja2hHHuKSgFJ",
      "captainid": "user::LiZNrnif5zw2HXwAuhzTZm",
      "crews": [
        {
          "checkedin": false,
          "email": "member-vQOmSlEd@foo.com",
          "name": "member-vQOmSlEd",
          "userid": "user::dCwUEgMcRK5LbwsFQ2vC4"
        }
      ],
      "sail": "oySqh3563"
    },
    {
      "boatid": "boat::iNYXs2w4dcNCYu8KSNrnzW",
      "captainid": "user::Kcc9tMGXFG2FBPAE6BocvS",
      "crews": [
        {
          "checkedin": false,
          "email": "member-NwpJnLhF@foo.com",
          "name": "member-NwpJnLhF",
          "userid": "user::2pbFimET4wrZgBr24SQxBM"
        }
      ],
      "sail": "SOijH4961"
    }
  ],
  "date": "2021-03-12",
  "fleets": [
    {
      "fleet": "J/24",
      "launchtime": "18:00"
    },
    {
      "fleet": "Spin A",
      "launchtime": "18:10"
    }
  ],
  "index": 1,
  "numboats": 3,
  "racecommittee": [
    {
      "email": "member1@foo.com"
    },
    {
      "email": "sailor1@foo.com"
    }
  ],
  "raceid": "1",
  "raceseriesid": "raceseries::1",
  "type": "race",
  "yachtclubid": "yachtclub::1"
}

and I want to return the following document:

[
{
  "boats": [
    {
      "boatid": "boat::L4tdDXPxyTyDrqQJRMWTUP",
      "captainid": "user::p4zMjxu6DvdRT3NZxyKm2E",
      "boatname": "HippyBoat-#10",
      "captain": "captain-AyzaZWan",
      "fleet": "Melges",
      "phrf": 765,
      "crews": [
        {
          "checkedin": false,
          "email": "member-HmESvYQf@foo.com",
          "name": "member-HmESvYQf",
          "userid": "user::E9QJDzktNTSnTYm9W5aSrg"
        },
        {
          "checkedin": false,
          "email": "member-mwTXtIBc@foo.com",
          "name": "member-mwTXtIBc",
          "userid": "user::a5AczU4r22zWvhQcndSdhZ"
        }
      ],
      "sail": "fvYGZ637"
    },
    {
      "boatid": "boat::cBwGp93NCPja2hHHuKSgFJ",
      "captainid": "user::LiZNrnif5zw2HXwAuhzTZm",
      "boatname": "HippyBoat-#11",
      "captain": "captain-uGxrfdVQ",
      "fleet": "Melges",
      "phrf": 175,
      "crews": [
        {
          "checkedin": false,
          "email": "member-vQOmSlEd@foo.com",
          "name": "member-vQOmSlEd",
          "userid": "user::dCwUEgMcRK5LbwsFQ2vC4"
        }
      ],
      "sail": "oySqh3563"
    },
    {
      "boatid": "boat::iNYXs2w4dcNCYu8KSNrnzW",
      "captainid": "user::Kcc9tMGXFG2FBPAE6BocvS",
      "boatname": "HippyBoat-#9",
      "captain": "captain-NrMJAHhx",
      "fleet": "J/24",
      "phrf": 630,
      "crews": [
        {
          "checkedin": false,
          "email": "member-NwpJnLhF@foo.com",
          "name": "member-NwpJnLhF",
          "userid": "user::2pbFimET4wrZgBr24SQxBM"
        }
      ],
      "sail": "SOijH4961"
    }
  ],
  "date": "2021-03-12",
  "fleets": [
    {
      "fleet": "J/24",
      "launchtime": "18:00"
    },
    {
      "fleet": "Spin A",
      "launchtime": "18:10"
    }
  ],
  "index": 1,
  "numboats": 3,
  "racecommittee": [
    {
      "email": "member1@foo.com"
    },
    {
      "email": "sailor1@foo.com"
    }
  ],
  "raceid": "1",
  "raceseriesid": "raceseries::1",
   "rclist": [
      {
        "email": "member1@foo.com",
        "name": "member1"
      },
      {
        "email": "sailor1@foo.com",
        "name": "sailor1"
      }
    ],
  "type": "race",
  "yachtclubid": "yachtclub::1"
}
]

Here is the query I am using:

SELECT race.*,
       ARRAY_AGG(OBJECT_ADD(committee, 'name', member.name)) AS rclist,
       ARRAY_AGG({"boatname": boat.name, "fleet": boat.fleet, "phrf": boat.phrf, "captain": captain.name, "boatid": raceboat.boatid, "crews": raceboat.crews, "sail": boat.sail}) AS raceboats
FROM railmeat AS race
UNNEST race1.racecommittee AS committee
UNNEST race1.boats AS raceboat
    JOIN railmeat AS member ON committee.email = member.email
    JOIN railmeat AS boat ON raceboat.boatid = META(boat).id
    JOIN railmeat AS captain ON raceboat.captainid = META(captain).id
WHERE race.raceid='1'
    AND race.type='race'
GROUP BY race

Your answer was awesome as it allowed me to add the right elements to my object, but when I run the query, this is what I get:

[
{
  "boats": [
    {
      "boatid": "boat::L4tdDXPxyTyDrqQJRMWTUP",
      "captainid": "user::p4zMjxu6DvdRT3NZxyKm2E",
      "boatname": "HippyBoat-#10",
      "captain": "captain-AyzaZWan",
      "fleet": "Melges",
      "phrf": 765,
      "crews": [
        {
          "checkedin": false,
          "email": "member-HmESvYQf@foo.com",
          "name": "member-HmESvYQf",
          "userid": "user::E9QJDzktNTSnTYm9W5aSrg"
        },
        {
          "checkedin": false,
          "email": "member-mwTXtIBc@foo.com",
          "name": "member-mwTXtIBc",
          "userid": "user::a5AczU4r22zWvhQcndSdhZ"
        }
      ],
      "sail": "fvYGZ637"
    },
    {
      "boatid": "boat::L4tdDXPxyTyDrqQJRMWTUP",
      "captainid": "user::p4zMjxu6DvdRT3NZxyKm2E",
      "boatname": "HippyBoat-#10",
      "captain": "captain-AyzaZWan",
      "fleet": "Melges",
      "phrf": 765,
      "crews": [
        {
          "checkedin": false,
          "email": "member-HmESvYQf@foo.com",
          "name": "member-HmESvYQf",
          "userid": "user::E9QJDzktNTSnTYm9W5aSrg"
        },
        {
          "checkedin": false,
          "email": "member-mwTXtIBc@foo.com",
          "name": "member-mwTXtIBc",
          "userid": "user::a5AczU4r22zWvhQcndSdhZ"
        }
      ],
      "sail": "fvYGZ637"
    },
    {
      "boatid": "boat::cBwGp93NCPja2hHHuKSgFJ",
      "captainid": "user::LiZNrnif5zw2HXwAuhzTZm",
      "boatname": "HippyBoat-#11",
      "captain": "captain-uGxrfdVQ",
      "fleet": "Melges",
      "phrf": 175,
      "crews": [
        {
          "checkedin": false,
          "email": "member-vQOmSlEd@foo.com",
          "name": "member-vQOmSlEd",
          "userid": "user::dCwUEgMcRK5LbwsFQ2vC4"
        }
      ],
      "sail": "oySqh3563"
    },
    {
      "boatid": "boat::cBwGp93NCPja2hHHuKSgFJ",
      "captainid": "user::LiZNrnif5zw2HXwAuhzTZm",
      "boatname": "HippyBoat-#11",
      "captain": "captain-uGxrfdVQ",
      "fleet": "Melges",
      "phrf": 175,
      "crews": [
        {
          "checkedin": false,
          "email": "member-vQOmSlEd@foo.com",
          "name": "member-vQOmSlEd",
          "userid": "user::dCwUEgMcRK5LbwsFQ2vC4"
        }
      ],
      "sail": "oySqh3563"
    },
    {
      "boatid": "boat::iNYXs2w4dcNCYu8KSNrnzW",
      "captainid": "user::Kcc9tMGXFG2FBPAE6BocvS",
      "boatname": "HippyBoat-#9",
      "captain": "captain-NrMJAHhx",
      "fleet": "J/24",
      "phrf": 630,
      "crews": [
        {
          "checkedin": false,
          "email": "member-NwpJnLhF@foo.com",
          "name": "member-NwpJnLhF",
          "userid": "user::2pbFimET4wrZgBr24SQxBM"
        }
      ],
      "sail": "SOijH4961"
    },
    {
      "boatid": "boat::iNYXs2w4dcNCYu8KSNrnzW",
      "captainid": "user::Kcc9tMGXFG2FBPAE6BocvS",
      "boatname": "HippyBoat-#9",
      "captain": "captain-NrMJAHhx",
      "fleet": "J/24",
      "phrf": 630,
      "crews": [
        {
          "checkedin": false,
          "email": "member-NwpJnLhF@foo.com",
          "name": "member-NwpJnLhF",
          "userid": "user::2pbFimET4wrZgBr24SQxBM"
        }
      ],
      "sail": "SOijH4961"
    }
  ],
  "date": "2021-03-12",
  "fleets": [
    {
      "fleet": "J/24",
      "launchtime": "18:00"
    },
    {
      "fleet": "Spin A",
      "launchtime": "18:10"
    }
  ],
  "index": 1,
  "numboats": 3,
  "racecommittee": [
    {
      "email": "member1@foo.com"
    },
    {
      "email": "sailor1@foo.com"
    }
  ],
  "raceid": "1",
  "raceseriesid": "raceseries::1",
   "rclist": [
      {
        "email": "member1@foo.com",
        "name": "member1"
      },
      {
        "email": "member1@foo.com",
        "name": "member1"
      },
      {
        "email": "member1@foo.com",
        "name": "member1"
      },
      {
        "email": "sailor1@foo.com",
        "name": "sailor1"
      },
      {
        "email": "sailor1@foo.com",
        "name": "sailor1"
      },
      {
        "email": "sailor1@foo.com",
        "name": "sailor1"
      }
    ],
 
  "type": "race",
  "yachtclubid": "yachtclub::1"
}
]

where each boat is repeated as many times as the number of elements in the racecommittee array and each element in rclist is repeated as many times as the number of boats in the boats array. I understand that the reason for this is that UNNEST does a join with the parent document and as such results in as many copies of that document as there are elements in the array. I thought this could be fixed with the GROUP BY clause, but only if I do a single UNNEST.

What I don’t know if how to fix this.

I have tried removing the UNNEST and instead do the following query:

SELECT race.*,
       ARRAY_AGG(OBJECT_ADD(committee, 'name', member.name)) AS rclist,
       ARRAY_AGG({"boatname": boat.name, "fleet": boat.fleet, "phrf": boat.phrf, "captain": captain.name, "boatid": raceboat.boatid, "crews": raceboat.crews, "sail": boat.sail}) AS raceboats
FROM railmeat AS race
    JOIN railmeat AS member ON ANY committee IN race.racecommittee SATISFIES committee.email = member.email END
    JOIN railmeat AS boat ON ANY raceboat IN race.boats SATISFIES raceboat.boatid = META(boat).id END
    JOIN railmeat AS captain ON ANY rboat IN race.boats SATISFIES rboat.captainid = META(captain).id END
WHERE race.raceid='1'
    AND race.type='race'
GROUP BY race

but then raceboat can’t be used to create the object in the SELECT clause.

How do I need to change the query so that it returns what I need? I feel like I am super close, but that I am totally missing something. Thoughts?

Thanks a lot again for your help. You are absolutely awesome.
Bertrand.

6

@Bertrand_Sirodot ,

Try DISTINCT and see if that solves your issue.

ARRAY_AGG( DISTINCT OBJECT_ADD(committee, 'name', member.name)) AS rclist,
       ARRAY_AGG( DISTINCT {"boatname": boat.name, "fleet": boat.fleet, "phrf": boat.phrf, "captain": captain.name, "boatid": raceboat.boatid, "crews": raceboat.crews, "sail": boat.sail}) AS raceboats

If not , What is document key for member. Post document key, document of member.

Or try this

SELECT race.*,
       (ARRAY OBJECT_CONCAT({v.crews, "captain":captainsobj.[v.captainid]}, boatsobj.[v.boatid])
       FOR v IN race.boats END) AS raceboats,
       ARRAY_AGG({"boatname": boat.name, "fleet": boat.fleet, "phrf": boat.phrf, "captain": captain.name, "boatid": raceboat.boatid, "crews": raceboat.crews, "sail": boat.sail}) AS raceboats
FROM (SELECT r.*, ARRAY_AGG(DISTINCT OBJECT_PUT(rc, "name", member.name)) AS rclist
      FROM railmeat AS r
      UNNEST r.racecommittee AS rc
      JOIN FROM railmeat AS member ON rc.email = member.email
      WHERE r.raceid = '1' AND r.type = 'race'
      GROUP BY r) AS race
  LET boatsobj = (OBJECT v.boatid:v
              FOR v IN (SELECT boat.name AS boatname, boat.fleet, boat.phrf, META(boat).id AS boatid, boat.sail
                        FROM railmeat AS boat USE KEYS race.boats[*].boatid)
              END) ,
  captainsobj = (OBJECT v.captainid:v.captain
             FOR v IN (SELECT captain.name AS captain, META(captain).id AS captainid
                       FROM railmeat AS captain USE KEYS race.boats[*].captainid)
             END)
7

Hi @vsr1 ,

adding DISTINCT did the trick. I had tried to add it before, but added it before the ARRAY_AGG function and that didn’t work.

I am curious about the second solution you are suggesting though, so I will spend some time digesting it.

As per your request, here is what a member document looks like:

{
  "userid": "1",
  "name": "captain1",
  "nickname": "AweSomeCap",
  "age": null,
  "dob": null,
  "phone_number": "111-222-3333",
  "email": "captain1@foo.com",
  "yachtclub_membership": [
    {
      "yachtclubid": "yachtclub::1",
      "member_number": 1111
    },
    {
      "yachtclubid": "yachtclub::2",
      "member_number": 1111
    }
  ],
  "city": "tampa",
  "state": "florida",
  "zip": "123456",
  "registeredon": "2021-01-01",
  "subscription": true,
  "role": "captain",
  "type": "user"
}

and this would be stored as user::1.

Thanks a lot again for the help. This has been eye opening on the power of N1QL.
Bertrand.

8

@Bertrand_Sirodot ,

Glad DISTINCT worked. DISTINCT outside the aggregates must be allowed on after SELECT (whole projection) MB-38208.

I was looking member document thought email as document key. It is not. To avoid UNNEST/JOIN you need correlated subquery. correlated subquery has restriction (removed upcoming 7.0 release). MB-30813

9

Thank you @vsr1 … I have looked at correlated subqueries, but, to be honest, my understanding of them and where they need to be used is fairly rudimentary right now, so it is definitely something i need to spend some time on.

Thank you again for your invaluable help.
Bertrand.