1

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
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Ajith
  • 325
  • 4
  • 17
  • Theres an s after the semicolon? Also the nested replaces are painful here. Also why not do the extraction once and use those variables in your query instead? – Shawn Aug 02 '21 at 14:08

1 Answers1

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828