mysql - sails.js 查询表列内的 json 对象

Question

所以这一定是个奇怪的问题，我要sails.js ORM 搜索如下内容

如果这是下面查询的结果

Venue.findOne({id: 125274827508536}).exec()

返回>

{
   "id": "125274827508536",
   "attire": "Casual",
   "can_post": false,
   "category": "Restaurant/cafe",
   "category_list": [
      {
         "id": "200742186618963",
         "name": "Vegetarian & Vegan Restaurant"
      },
      {
         "id": "192108214153222",
         "name": "Breakfast & Brunch Restaurant"
      },
      {
         "id": "188296324525457",
         "name": "Sandwich Shop"
      }
   ],
   "checkins": 562,
   "cover": {
      "cover_id": 356427064393310,
      "offset_x": 0,
      "offset_y": 13,
      "source": "https://fbcdn-sphotos-c-a.akamaihd.net/hphotos-ak-xaf1/t31.0-8/s720x720/460144_356427064393310_1179113344_o.jpg",
      "id": "356427064393310"
   },
   "culinary_team": "Ramy Abu-Yousef : Owner, Chef\nDallas Jones: Chef",
   "description": "Unique Sandwiches\nDelicious Salads\nHomemade Soups (4 daily)\nFresh Fruit Smoothies\nMilkshakes\nMOUSTACHE WALL OF FAME",
   "general_manager": "Ramy Abu-Yousef & Syndey Friedemann",
   "has_added_app": false,
   "hours": {
      "mon_1_open": "08:00",
      "mon_1_close": "22:00",
      "tue_1_open": "08:00",
      "tue_1_close": "22:00",
      "wed_1_open": "08:00",
      "wed_1_close": "22:00",
      "thu_1_open": "08:00",
      "thu_1_close": "22:00",
      "fri_1_open": "08:00",
      "fri_1_close": "22:00",
      "sat_1_open": "08:00",
      "sat_1_close": "22:00",
      "sun_1_open": "08:00",
      "sun_1_close": "22:00"
   },
   "is_community_page": false,
   "is_published": true,
   "likes": 540,
   "link": "https://www.facebook.com/JohnnyBarrs",
   "location": {
      "city": "Queenstown",
      "country": "New Zealand",
      "latitude": -45.032691433795,
      "longitude": 168.66154298959,
      "street": "15 Church Street",
      "zip": "9300"
   },
   "name": "Johnny Barr's",
   "parking": {
      "lot": 1,
      "street": 1,
      "valet": 0
   },
   "payment_options": {
      "amex": 1,
      "cash_only": 0,
      "discover": 0,
      "mastercard": 1,
      "visa": 1
   },
   "phone": "+64 (0)3 409 0169",
   "price_range": "$$ (10-30)",
   "restaurant_services": {
      "delivery": 1,
      "catering": 0,
      "groups": 1,
      "kids": 1,
      "outdoor": 0,
      "reserve": 0,
      "takeout": 1,
      "waiter": 0,
      "walkins": 1
   },
   "restaurant_specialties": {
      "breakfast": 1,
      "coffee": 1,
      "dinner": 1,
      "drinks": 1,
      "lunch": 1
   },
   "talking_about_count": 2,
   "username": "JohnnyBarrs",
   "website": "www.johnnybarrs.com",
   "were_here_count": 562
}

现在我需要 sails 做的是以下

Venue.findOne({'restaurant_services': {'delivery': 1}).exec()

要返回与上面所示相同的对象，

有什么想法吗？

Answer 1

如果你的适配器是 mongo 那么你可以开箱即用

Venue.findOne({'restaurant_services.delivery': 1}).exec()

因此，如果您可以将数据转换为另一个来源以执行此查询，您应该考虑您的选择。

如果不是，并且您的适配器是基于 SQL 的，那么它会更加困难并且需要有关您的用例的更多信息来决定最有效的选项。

例如，如果您可以将查询限制在 SQL 数据库中的几个索引字段，那么您可以使用 lodash 在您的记录中查找您的记录。

Venue.find({/*limiting criteria to bring down the number of results*/}).exec(function(err,results){/* JSON.parse(results) then use lodash to find the final result*/})

另一种选择是

Venue.find({restaurant_services: {contains: 'delivery: 1'}}).exec()

这是一个开箱即用的想法，但可能会奏效。

再次很大程度上取决于您的设置、它的索引方式以及需要找到多少个场所。