Need some help with the MySQL query.
I have a table named custom_fields with attributes id, user_id, name, value etc.
name and values are of type string and it can store sometimes JSON also in the form of a string.
Requirement:
I need to list out all the unique user_id which satisfies below conditions
1) name must be starting with street_address_
2) value is a hash but stored as a string. if the country code is in ('us', 'in')
Here is the sample record.
id: 90489,
user_id: 30207,
name: "street_address_billing",
value:"{\"street_address1\":\"401 Lenora Street\",\"street_address2\":\"\",\"city\":\"Belltown\",\"locality\":\"Seattlel\",\"province\":\"WA\",\"postal_code\":\"111\",\"country_code\":\"us\"}",
deleted_at: nil,
active: true
Here is the query I'm trying. But it is not working. I have no error but it does not give any results either. instead of in if i use = and single value sometimes it gives the value.
SELECT id,user_id, @addr:= replace(replace(replace(replace(value, ':“', ':"'), '”,', '",'), '”}', '"}'), '{“' , '{"'), JSON_EXTRACT(@addr, '$.country_code') as billing_country
FROM `custom_fields` WHERE `custom_fields`.`active` = TRUE AND (name REGEXP '^street_address_')
AND JSON_EXTRACT(@addr, '$.country_code') in ('us', 'in');s
Despite the fact that SELECT syntax puts the select-list above the WHERE clause, WHERE conditions are evaluated first, to restrict the rows returned by the query. Then for those rows only, expressions in the select-list are evaluated. So your variables defined in the select-list cannot be used in the WHERE clause.
So you need to reference the column itself in your WHERE conditions:
SELECT id,user_id, JSON_UNQUOTE(JSON_EXTRACT(value, '$.country_code')) AS billing_country
FROM `custom_fields` WHERE `custom_fields`.`active` = TRUE AND (name REGEXP '^street_address_')
AND JSON_UNQUOTE(JSON_EXTRACT(value, '$.country_code')) IN ('us', 'in');
Also you need to use JSON_UNQUOTE() because the return value of JSON_EXTRACT() is not a plain string — it's a JSON document that happens to be a scalar. So it will return "us".
I've removed all the replace() stuff because you shouldn't need it. If you use JSON data, you should store it in a JSON column, and that will reject invalid JSON that contains smart-quote characters.