N1QL retreive document with reference to other documents

SQL++
1

Hello!,

I’m using Couchbase-server 5.0.1-5003-community I have a schema as such:

{
    "_id": "xxx-xxx-xxx",
    "type": "foo",
    "targets": [
        {
            "label": "xyz",
            "code": "001"
        },
        {
            "label": "abc",
            "code": "002"
        }
    ]
}

Currently the value of targets is inputed directly. The items of the targets array may change in future, which means One would need to run an update query for every occurrence, which isn’t a good idea. So Instead I want to move the targets, into their own separate documents with schema:

{
    "id": "xxx-xxx-xxx",
    "type": "target",
    "label": "xyz",
    "code":"001"
}

And reference meta().id in the foo documents:

{
    "_id": "xxx-xxx-xxx",
    "type": "foo",
    "targets": [
        "target_001",
        "target_002"
    ]
}

The values of the targets array above are document keys to the target doc.

How can run a N1QL on the above document(s) to resolve the following schema?:

{
    "_id": "xxx-xxx-xxx",
    "type": "foo",
    "targets": [
        {
            "label": "xyz",
            "code": "001"
        },
        {
            "label": "abc",
            "code": "002"
        }
    ]
}

Thanks
cc: @geraldss @vsr1 @ingenthr

2 
In 5.0.1 type target document needs to have document keys as "target_001",....
SELECT f, ARRAY_AGG({t.label, t.code}) AS targets FROM default AS f JOIN default AS t ON KEYS f.targets WHERE f.type = "foo" AND t.type = "target" GROUP BY f ;

OR

SELECT f,  t AS targets FROM default AS f NEST default AS t ON KEYS f.targets WHERE f.type = "foo" ;

In 5.5.0 you can use ANSI JOIN

Did you take look UPDATE FOR UPDATING(last example) arrays.

3

Hey thanks, the second solution seems to work, the first, when I ran it threw and error:

[
  {
    "code": 4210,
    "msg": "Expression must be a group key or aggregate: `f`",
    "query_from_user": "SELECT f, ARRAY_AGG({t.label, t.code}) AS targets FROM default AS f \nJOIN default AS t ON KEYS f.targets WHERE f.type = \"foo\" \nAND t.type = \"target\" GROUP BY META(f).id ;"
  }
]
4

Updated previous post. try again

1 Like
5

Thanks @vsr1 both works

1 Like