JSON 数组上的 MySQL 通配符

Question

我正在尝试使用通配符提取 JSON 中包含的数组内的 ID (5384)。我遇到的问题是 ID 的位置对于该数组中的每个元素都没有固定位置。我在该数组中的 JSON 示例如下(其中“id”:5384 可能占据不同的索引位置):

{  
  "id":7465115,
  "name":"BCA_WS_FBX_Nielsen PRIZM_Test_Unlock_1x1",
  "advertiser_id":155085,
  "pixels":[  
  {  
   "id":416491,
   "pixel_template_id":null,
  },
  {  
   "id":5384,
   "pixel_template_id":null,
  }
  ]
}

我的查询如下:

SELECT id, json FROM PROD_APPNEXUS.dimension_json_creatives 
WHERE JSON LIKE ('%pixels%_%"id":5384,%') AND MEMBER_ID = 364

我试图只提取像素数组中 ID 为 5384 的项目。

任何关于如何实现这一点的评论都将受到高度重视，谢谢!

更新:MySQL 版本 5.6.17

山姆

Answer 1

尝试:

12.6 The JSON Data Type . MySQL 5.7.8+

SELECT
  `id`,
  `json` -- Data Type JSON
FROM
  `PROD_APPNEXUS`.`dimension_json_creatives`
WHERE
  JSON_CONTAINS(`json`, '{"id": 5384}', '$.pixels') AND
  `MEMBER_ID` = 364;

更新

使用 5.6.17，一个选项是:

SELECT
  `id`,
  `json`
FROM
  `PROD_APPNEXUS`.`dimension_json_creatives`
WHERE
  `json` REGEXP '"pixels":\\[.*"id":5384.*]' AND
  MEMBER_ID = 364;

性能可能会受到影响，具体取决于表中元组的数量。