N1QL help with nested array

SQL++
1

Hi,

I have a json like this:

{
  "topLevel": "1",
  "childArray": [
    {
      "a2": "o",
      "inner": [
        {
          "innera1": "innerv1",
          "innerb1": "innerb1"
        }
      ]
    },
    {
      "a2": "au",
      "inner": [
        {
          "innera1": "innerv1-v2",
          "innerb1": "innerb1-v2"
        },
        {
          "innera1": "innerv1-v3",
          "innerb1": "innerb1-v3"
        }
      ]
    }
  ]
}

I want the n1ql to merge and fetch all values in childArray having a2, and innera1

The response should look like this

{
  "topLevel": "1",
  "childArray": [
    {
      "a2": "o",
      "innera1": "innerv1"
    },
    {
      "a2": "au",
      "innera1": "innerv1-v2"
    },
    {
      "a2": "au",
      "innera1": "innerv1-v3"
    }
  ]
}

Any suggestion to achieve this ?

2

Is this question for CB Server N1QL or Mobile N1QL?

3

I need the N1QL for CB Server. We are currently on 6.6

4 
WITH docs AS ({ "topLevel": "1", "childArray": [ { "a2": "o", "inner": [ { "innera1": "innerv1", "innerb1": "innerb1" } ] }, { "a2": "au", "inner": [ { "innera1": "innerv1-v2", "innerb1": "innerb1-v2" }, { "innera1": "innerv1-v3", "innerb1": "innerb1-v3" } ] } ] })
SELECT d.*, 
      (SELECT c.a2, i.innera1 FROM d.childArray AS c UNNEST c.`inner` AS i) AS childArray
FROM docs AS d;
5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.