AS aliasing on sub-documents

SQL++
1

This query works:

SELECT `value`.`net` FROM fooBucket;

This query does not work:

SELECT `value`.`net` AS `somethingElse`.`net` FROM fooBucket;

Why is that? If I select a sub-document, then what comes back preserves structure, but I can’t re-name or change that structure with an alias? Indeed it looks like even if I alias it to itself (i.e. ```SELECT value.net AS value.`net````) it doesn’t work, saying “syntax error at .”. If you alias, must the name always be “flat”?

2

Interesting question. We will consider this for the future.

For now, you can do the following:

SELECT { “net”: `value`.net } AS somethingElse
FROM …;

3

You can use object_add(somethingElse, “net”, value) as somethingElse

4

But it can be hard to read very fast :

SELECT 
  object_put(
     object_put(@city, "id", meta(@city).id),
     "mayor", object_put(@mayor, "id", meta(@mayor).id))
     as city

Instead of :

SELECT
  @city,
  meta(@city).id as city.id,
 @mayor as city.mayor,
 meta(@mayor).id as city.mayor.id
5

You can use back ticks to escape any alias:

SELECT “Portland” AS `country.state.city`;

6

Yes i Know, i only show the final version of SELECT when Couchbase will support this syntaxe to aliasing on sub-documents.

7

Ok, thanks. LMK if there is something you are suggesting that we add.

8

I hope you’ll add the feature to use alias to fill sub-documents.

9

Can you give an example. I want to be sure I understand.

10 
SELECT 
    @city,
    meta(@city).id AS city.id,
    @mayor AS city.mayor,
    meta(@mayor).id AS city.mayor.id
FROM `odm-test` AS city
JOIN `odm-test` AS mayor ON KEYS city.mayorId
WHERE city.type = "city" AND mayor.type = "mayor"
LIMIT 1

Should produce :

 [
  {
    "city": {
      "country": {
        "name": "France",
        "type": "country"
      },
      "geo": {
        "lat": 37.7825,
        "lon": -122.393
      },
      "id": "city-1",
      "mayor": {
        "firstname": "Grégoire",
        "id": "mayor-1",
        "lastname": "Lasteyrie",
        "type": "mayor"
      },
      "mayorId": "mayor-1",
      "name": "Palaiseau",
      "type": "city"
    }
  }
]

Like this query :

SELECT 
   object_put(
      object_put(@city, "id", meta(@city).id),
      "mayor", object_put(@mayor, "id", meta(@mayor).id))
    as city
FROM `odm-test` AS city
JOIN `odm-test` AS mayor ON KEYS city.mayorId
WHERE city.type = "city" AND mayor.type = "mayor"
LIMIT 1

But with a very cool and simple syntax :slight_smile:

11

Got it, thanks. We would need a general syntax to combine objects and arrays, which would be usable beyond projections and aliases. We will keep that in mind. For example:

SELECT
    city ++
    {
        meta(city).id,
        "mayor": mayor ++ { city.mayor.id }
    } AS city
FROM `odm-test` AS city
JOIN `odm-test` AS mayor ON KEYS city.mayorId
WHERE city.type = "city" AND mayor.type = "mayor"
LIMIT 1;
12

The exact syntax will be :

SELECT
    city ++
    {
        "id": meta(city).id,
        "mayor": mayor ++ { "id": meta(mayor).id }
    } AS city
FROM `odm-test` AS city
JOIN `odm-test` AS mayor ON KEYS city.mayorId
WHERE city.type = "city" AND mayor.type = "mayor"
LIMIT 1

It seems more complex to read without any benefits, no ?

13

I was not suggesting an exact syntax. I was just framing the issue. We need a syntax for combining objects and arrays. It must work anywhere that objects and arrays can appear. It cannot be specific to, or limited to, aliases and projections.

Using functions to combine objects and arrays is a bit verbose, and more concise syntax would be nice.

14

A combination of both could be cool.

SELECT
    meta(mayor).id AS mayor.id,
    city ++
    {
        "id": meta(city).id,
        "mayor": mayor
    } AS city
FROM `odm-test` AS city
JOIN `odm-test` AS mayor ON KEYS city.mayorId
WHERE city.type = "city" AND mayor.type = "mayor"
LIMIT 1
15

So @geraldss any new about this ?

16

We added one step. For the next step, I am thinking we could support concatenation ( || ) of objects and of arrays.

Here is what we added:

SELECT
    OBJECT_CONCAT( city, { META(city).id }, { "mayor": OBJECT_CONCAT( mayor, { META(mayor).id } ) } ) AS city
FROM `odm-test` AS city
JOIN `odm-test` AS mayor ON KEYS city.mayorId
WHERE city.type = "city" AND mayor.type = "mayor"
LIMIT 1
;
17

Ok nice.
So META(city).id is a special case ? and its magically mapped into { “id”: META(city).id } ?

18

Not a special case. For any expression that ends with an identifier x:

{ expr.x }

is mapped to:

{ "x": expr.x }

19

Ok nice, thank you :slight_smile:

1 Like