Sam Comber Sam Comber - 7 months ago 16
SQL Question

MySQL Wildcard on JSON array

I'm trying to extract an ID (5384) that is inside an array contained in JSON using wildcards. The problem I'm having is that the position of the ID doesn't have a fixed position for each element in that array. An example of my JSON in that array is like this (where "id":5384 could occupy different indexed positions):

{
"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,
}
]
}


My query is as follows:

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


I'm trying to extract only items that are in the pixels array and have an ID of 5384.

Any comments as to how to achieve this would be highly valued, thanks!

UPDATE: MySQL version 5.6.17

Sam

Answer

The only way I would think of is to use the REGEXP syntax of MySQL:

SELECT id, json FROM PROD_APPNEXUS.dimension_json_creatives 
WHERE (JSON REGEXP '("pixels":\[)?.*"id":5384') AND MEMBER_ID = 364