Selecting from subquery

SQL++
1

Hello,

I’ve got a document with structure like that:

{
    ...
    "elements": [
        {"foo": "bar", "value": 123},
        {"foo": "baz", "value": 456},
        ...
    ],
    ...
}

I’m trying to select values from that elements array. I’ve tried this:

SELECT a.* FROM (SELECT elements FROM default WHERE Meta().id = <my document's id>) WHERE a.foo = "bar"

but it doesn’t seems to work. Can I do this?

BTW I’m using couchbase server version 4.5 if that matters.

2

Each sub-object as separate object

SELECT e 
FROM default AS d
UNNEST d.elements AS e
WHERE Meta(d).id =  "id" AND e.foo  = "bar";

OR

Original format and filter array elements

SELECT d.*, ARRAY v FOR v IN d.elements WHEN v.foo = "bar" END AS elements
FROM default AS d
WHERE Meta(d).id =  "id" ;