Doing something wrong with JSON_EXTRACT and mysql local variable

Question

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

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
– Shawn, Commented Aug 2, 2021 at 14:08

Bill Karwin · Accepted Answer · 2021-08-02 14:44:49Z

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.

answered Aug 2, 2021 at 14:44

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ajith Over a year ago

Thank you for your the solution with the explaination.

Collectives™ on Stack Overflow

Doing something wrong with JSON_EXTRACT and mysql local variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related