Q: object connect query with N1QL

SQL++
1

HI @vsr1:

the first document is promotion, the struct as follow:

[
  {
    "promotionDishList": {
      "Dish.006be66f-53bf-4605-882f-52413e691e8d": {
        "count": 1,
        "price": 32
      },
      "Dish.0e23eeca-382b-488e-ba1a-82b7ef553b9f": {
        "count": 1,
        "price": 32
      }
  }
]

the second document is dishes, the struct as follow:

[
  {
    "id": "Dish.006be66f-53bf-4605-882f-52413e691e8d",
    "kitchen": {
      "channelId": "733d2f51",
      "className": "Dish",
      "code26": "HZMDF",
      "code9": "49633",
      "kindId": "DishesKind.0f458808-5ed1-4b29-ba8d-fc4d54b1fe51",
      "name": "doufu",
      "price": 32,
      "sell": false,
      "sortNum": 99
    }
  }
]

I want query the dishes name iterate by the of promotionDishList object, how can I write the N1QL?

thanks!

angular

2 
SELECT d. kitchen.name, d.id  
FROM default AS d1 
JOIN default AS d  ON KEYS OBJECT_NAMES(d1.promotionDishList)
WHERE d1.type = "promotionList";

OR

CREATE INDEX ix1 ON default (kitchen.id, kitchen.name) WHERE kitchen.className = "Dish";
SELECT d. kitchen.name, d.id  
FROM default AS d1 
UNNEST OBJECT_NAMES(d1.promotionDishList) AS dname
JOIN default AS d  ON dname = d.id
WHERE d1.type = "promotionList" AND d.kitchen.className = "Dish";
3

HI @vsr1:
Thank you for your prompt reply. N1QL statement works fine. thanks again.
angular

4

HI @vsr1,

if I want add the count to result as follow:

{
   "name": "doufu",
   "count":  4
}

how can I wirte N1QL?

thanks

angular

5

Do aggregate query.

SELECT name, count(1) FROM …
GROUP BY name;

6

HI @vsr1,
sorry, I haven’t explained clean the question. I want to add count property of the promotionDishList object
the promotionDishList contains dynamic key dish.xxxxxxx, so I don’t know how write the n1ql statement.

thanks!
angular

7

Tell me which query you are using so that i can modify and suggest solution.

8

HI @vsr1,

THANKS for your reply.

the whole promotion document as follow:

[
  {
    "kitchen": {
      "_id": "Promotion.9c35b903-4df7-493f-b7f2-816a6edd55c8",
      "_rev": "2-bdbeb19ebb519ef55694fb2966be8b96",
      "channelId": "733d2f51",
      "className": "Promotion",
      "dataType": "BaseData",
      "name": "308套餐",
      "owner": "733d2f51_casher",
      "promotionDishList": {
        "Dish.006be66f-53bf-4605-882f-52413e691e8d": {
          "count": 1,
          "price": 32
        },
        "Dish.36d6a59f-5b85-4686-9381-bfcc41636cfc": {
          "count": 1,
          "price": 78
        },
        "Dish.405673dd-7248-4e6e-ba2e-6e339143b1ea": {
          "count": 1,
          "price": 58
        },
        "Dish.45d0d8c4-b526-402f-9b26-32500ea943f4": {
          "count": 1,
          "price": 58
        },
        "Dish.4cb2a2ed-19bd-4ac7-ba85-227e4295b0fe": {
          "count": 1,
          "price": 28
        },
        "Dish.7366d6d5-a2d8-441d-9b84-14f31785ce5d": {
          "count": 1,
          "price": 68
        },
        "Dish.8f84d1e0-23ae-4166-974f-1c347d198246": {
          "count": 5,
          "price": 3
        },
        "Dish.a2422caa-1b41-4687-aadb-c339cb414635": {
          "count": 1,
          "price": 38
        },
        "Dish.f6005260-6078-4532-94de-d1476c7b3e7f": {
          "count": 1,
          "price": 48
        }
      },
      "promotionRuleList": {},
      "promotionType": 3
    }
  }
]

and the one of dishes document as follow:

[
  {
    "kitchen": {
      "channelId": "733d2f51",
      "className": "Dish",
      "code26": "HZMDF",
      "code9": "49633",
      "kindId": "DishesKind.0f458808-5ed1-4b29-ba8d-fc4d54b1fe51",
      "name": "徽州毛豆腐",
      "price": 32,
      "sell": false,
      "sortNum": 99
    }
  }
]

I write a N1QL statement as follow:

SELECT pm.promotionType as type, dish.name as dishName, dish.kindId, meta(dish).id as dishId
   ,(SELECT raw p.val.count from OBJECT_PAIRS(pm.promotionDishList) as p
         where p.name = meta(dish).id) as count
   FROM kitchen AS pm
   JOIN kitchen AS dish  ON KEYS OBJECT_NAMES(pm.promotionDishList)
   WHERE pm.className = 'Promotion'
   AND meta(pm).id = "Promotion.9c35b903-4df7-493f-b7f2-816a6edd55c8"

the result is:

[
  {
    "count": [
      1
    ],
    "dishId": "Dish.006be66f-53bf-4605-882f-52413e691e8d",
    "dishName": "徽州毛豆腐",
    "kindId": "DishesKind.0f458808-5ed1-4b29-ba8d-fc4d54b1fe51",
    "type": 3
  },
  {
    "count": [
      1
    ],
    "dishId": "Dish.36d6a59f-5b85-4686-9381-bfcc41636cfc",
    "dishName": "徽州三鲜锅",
    "kindId": "DishesKind.0f458808-5ed1-4b29-ba8d-fc4d54b1fe51",
    "type": 3
  }
]

Don’t know if I wrote this correctly?

  "count": [
      1
    ],

how can I convert it to:

 {
    "count": 1,
    "dishId": "Dish.006be66f-53bf-4605-882f-52413e691e8d",
    "dishName": "徽州毛豆腐",
    "kindId": "DishesKind.0f458808-5ed1-4b29-ba8d-fc4d54b1fe51",
    "type": 3
  }

thank you very much.
angular

9 
SELECT pm.promotionType AS type,
      dish.name AS dishName,
      dish.kindId,
      META(dish).id AS dishId
      pm.[META(dish).id].`count` AS count
FROM kitchen AS pm USE KEYS ["Promotion.9c35b903-4df7-493f-b7f2-816a6edd55c8"]
JOIN kitchen AS dish  ON KEYS OBJECT_NAMES(pm.promotionDishList)
WHERE pm.className = 'Promotion';


pm.[META(dish).id].`count` 
If you look above expression after dot there is  no field name it  has [expr] 
The expr is evaluated and takes value. If the  value must be string  
 this string is converted to fieldname and accessed  as
example:  pm.`Dish.405673dd-7248-4e6e-ba2e-6e339143b1ea`.`count`
10

HI @vsr1,

thank you very much! the N1QL like magic. :smiley:

angular

1 Like