Processing document which contains array of objects

SQL++
,
1

Hi, I have a query which works fine with two static arrays.

SELECT item FROM ['bmw', 'renault'] model
INNER JOIN (
SELECT car.* FROM 
[
        {
          "model": "bmw",
          "series": "x1"
        },
        {
          "model": "renault",
          "series": "captur"
        },
		{
		  "model":"suzuki",
		  "series":"vitara"
		}
        ] car) item
ON item.model = model

However, I need replace

[
        {
          "model": "bmw",
          "series": "x1"
        },
        {
          "model": "renault",
          "series": "captur"
        },
		{
		  "model":"suzuki",
		  "series":"vitara"
		}
        ]

to document with id car1 to Cars bucket.
I tried to rewrite my query but the new query does not work:

SELECT item FROM ['bmw', 'renault'] model
INNER JOIN (SELECT car.* FROM Cars car WHERE meta(car).id = 'car1') item
ON item.model = model

How to fix this query? I also don’t want to use IN, UNNEST operators as I already have just array of objects in my document. Maybe I need to change the document body ?

2 
SELECT item FROM ['bmw', 'renault'] model
INNER JOIN (SELECT car.* FROM Cars car USE KEYS "car1") item
ON item.model = model

Query seems right.
If already know document key use USE KEYS. Post the document of car1 . You might have ARRAY as field name.

3

Thanks for answer :slight_smile:
I played with ARRAY a lot. Did something like that(and other things):

SELECT item FROM ['bmw', 'renault'] model
INNER JOIN
(
  SELECT ARRAY v FOR v IN (
    SELECT car.* FROM Cars car WHERE meta(car).id = 'car1') END ) item
ON item.model = model

However, no results.

[
  {
    "$1": [
      {}
    ]
  }
]

To be honest I am NOT expert in Couchbase Array functionality.
Maybe I missed something.

4

@maksym.shulga ,

Please post the document of car1 i.e. results of following query

SELECT RAW car FROM Cars AS car USE KEYS "car1";

Also try this without JOIN

SELECT  ARRAY v FOR v IN car WHEN v.model IN ['bmw', 'renault']  END AS models 
 FROM Cars AS car USE KEYS "car1";
5

Thanks for helping. Now I learned RAW operator :slight_smile:
However, it also does not work.

[
  {
    "model": []
  }
]

I have noticed that

SELECT RAW car FROM Cars AS car USE KEYS "car1";

returns

[
[
        {
          "model": "bmw",
          "series": "x1"
        },
        {
          "model": "renault",
          "series": "captur"
        },
		{
		  "model":"suzuki",
		  "series":"vitara"
		}
        ] 
]

it is almost as I have(I need) for static car array but with RAW we have double [[]] instead of []. How can we delete extra [] ?
I also try to avoid IN operator as for 500 model it works VERY slowly :frowning:

6

@maksym.shulga

car is ARRAY not object, so you can’t do car.* (so use RAW car)
SELECT makes ARRAY and car is array so unnest required.

SELECT item FROM ['bmw', 'renault'] model
INNER JOIN (SELECT RAW c  FROM Cars AS car UNNEST car AS c WHERE meta(car).id = 'car1'  ) item
ON item.model = model;

Best will be

SELECT item.*
FROM Cars AS car USE KEYS "car1"
UNNEST car AS item 
WHERE item.model IN  ["bmw", "renault"]

Checkout IN clause optimization

Also checkout ARRAY_FLATTEN() Array Functions | Couchbase Docs

7

Thank you very much for help!
However, it does not work with UNNEST :frowning:
I think I need to read documentation carefully.

8 
insert into default values ("car1",[ { "model": "bmw", "series": "x1" }, { "model": "renault", "series": "captur" }, { "model":"suzuki", "series":"vitara" } ] );
SELECT item.* FROM default AS car USE KEYS "car1" UNNEST car AS item WHERE item.model IN  ["bmw", "renault"] ;

{
    "requestID": "64452322-6745-49c2-b9ba-c8d61784db0f",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "model": "bmw",
        "series": "x1"
    },
    {
        "model": "renault",
        "series": "captur"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "976.554µs",
        "executionTime": "920.927µs",
        "resultCount": 2,
        "resultSize": 116
    }
}


SELECT item FROM ['bmw', 'renault'] model INNER JOIN (SELECT RAW c  FROM default AS car UNNEST car AS c  WHERE meta(car).id = 'car1'  ) item ON item.model = model;
{
    "requestID": "d9ec46c0-df7b-48c1-a668-40bf01734270",
    "signature": {
        "item": "json"
    },
    "results": [
    {
        "item": {
            "model": "bmw",
            "series": "x1"
        }
    },
    {
        "item": {
            "model": "renault",
            "series": "captur"
        }
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.50319ms",
        "executionTime": "2.451617ms",
        "resultCount": 2,
        "resultSize": 188
    }
}
9

Thanks a million!
It’s works.
I think I had some problems with previous queries as I added initial data manually with help of IDEA without insert operator, but it does not matter now.

10

it does not work with UNNEST

11

@aonesur , Could you please more details of what does not work With your query.

12

i tried the code given above..

13

I have provide with results. Will you provide your insert/select/results.