Concatenate specific values from an array of objects into a single string

SQL++
,
1

I’m trying to create a N1QL query to flatten some of the data from our documents so it can be exported to csv and consumed by something like Excel. It gets a little complicated because the values I want to concatenate are in an array of objects nested inside another array of objects.

The document format is something like this:

{
  "dispatchTime": "2017-09-15T21:08:00Z",
  "crewMembers": [
    {
      "firstName": "Chris",
      "lastName": "Bacon",
      "middleName": "P.",
      "roles": [
        {
          "key": "a14ab9b2-6fa0-e211-90f1-005056c00008",
          "value": "Driver\/Pilot - Response"
        },
        {
          "key": "2621ab0d-d9ad-e211-8dad-005056c00008",
          "value": "Primary Caregiver - Transport"
        }
      ],
      "userKey": "03451b0c-f3a2-e711-80ef-005056a8748b"
    },
    {
      "firstName": "John",
      "lastName": "Walker",
      "middleName": "E.",
      "roles": [
        {
          "key": "fc6ab9b2-6fa0-e211-90f1-005056c00008",
          "value": "Driver\/Pilot - Transport"
        },
        {
          "key": "d638a630-5cf5-4caf-9346-5cf1d47c5573",
          "value": "Primary Caregiver - Scene"
        }
      ],
      "userKey": "9c6d0558-0aa2-e711-80ef-005056a8748b"
    }
  ]
}

The query I have so far:

SELECT meta(doc).id AS id,
  doc.dispatchTime,
  crew.firstName,
  crew.lastName,
  crew.userKey as key,
  doc.tenantKey
FROM `bucket` doc
UNNEST doc.crewMembers AS crew

We need the roles.value strings concatenated together in a single string in the query response. It looks like it should be possible to do it with something like “FOR role IN crew.roles END” but I’m not sure how to concatenate the role.value properties into a single string.

1 Like
2

Check the following query if that works for your case

SELECT meta(doc).id AS id,
  doc.dispatchTime,
  crew.firstName,
  crew.lastName,
  crew.userKey as key,
  doc.tenantKey,
 REPLACE(REPLACE(
           REPLACE(ENCODE_JSON(roles),
                   "\",\"",
                   ""),
           "[\"",
            ""),
   "\"]",
   "") AS roles
FROM `bucket` doc
UNNEST doc.crewMembers AS crew
LET roles = crew.roles[*].`value`;



SELECT
REPLACE(REPLACE(
               REPLACE(ENCODE_JSON(["a","b","c"]),
                       "\",\"",
                       ""),
               "[\"",
                ""),
       "\"]",
       "");

Alternative :
The looping construct will not use previous values.
If you know upfront approximately how many elements you have in array can do this.

SELECT meta(doc).id AS id,
  doc.dispatchTime,
  crew.firstName,
  crew.lastName,
  crew.userKey as key,
  doc.tenantKey,
  IFMISSINGORNULL(roles[0],"") ||  IFMISSINGORNULL(roles[1],"") ||  IFMISSINGORNULL(roles[2],"")  AS roles
FROM `bucket` doc
UNNEST doc.crewMembers AS crew
LET roles = crew.roles[*].`value`;

Repeat IFMISSINGORNULL(roles[num],"") maximum you think you have.

Opened Feature Improvement MB-27281

1 Like
3

I ended up using the ENCODE_JSON trick, but I used one TRIM and one REPLACE instead of three calls to REPLACE:

REPLACE( TRIM ( ENCODE_JSON(crew.roles[*].value), ‘"[]’), ‘","’, ', ') as roles,

Thanks for the suggestion!

1 Like