Question 46697 in Debian Question posted in
The oficial documentation can be found here.

Order of CAST() and COALESCE() matters in MariaDB – Debian

I have a strange problem: There is a price in a JSON column in a table and the following statements give different results while they should give the same thing:

CAST(COALESCE(JSON_EXTRACT(item.price_details, "$.shipping.price"), 0) AS FLOAT) AS shippricecoalfloat

COALESCE(CAST(JSON_EXTRACT(item.price_details, "$.shipping.price") AS FLOAT), 0) AS shippricefloatcoal

Just to check I also added a JSON_EXTRACT(item.price_details, "$.shipping.price") AS shipprice

Result:

results

MariaDB version: mariadb Ver 15.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

DB Fiddle (I couldn’t use the same MariaDB version but it behaves the same anyways apparently)

Tags:

2

Answers


  1. 0

    I believe this is "the solution" for your question:

    select cast('"0.8648"' as float)

    I will try to explain how I see it…
    First thing to know is: "COALESCE() has the same data type as the argument."
    This COALESCE(JSON_EXTRACT(price_details, "$.shipping.price"), 0) as coal returns "0.8648" but as a string(varchar) so it returns ‘"0.8648"’

    This CAST(JSON_EXTRACT(price_details, "$.shipping.price") AS FLOAT) as caast returns 0.8648 so no problems there…

    This select cast('"0.8648"' as float) returns 0 which is the same as your result…

    You can not cast a value with double quotes in it to a float.
    You will get a result when you coalesce float value and 0 equal to that float value.

    I believe this explains it ?

    DEMO

    Maybe as addition to this I should ask/say isn’t this "098" same as this ‘098’ ?

    Login or Signup to reply.
  2. 0

    You’re using the wrong JSON function.

    JSON_EXTRACT() returns a JSON Object found at that path. That’s why in your fiddle you still see double quotes.

    You want to return a scalar value from a specific path. So, use JSON_VALUE()

    SELECT
    item_id,
    JSON_VALUE(price_details, "$.shipping.price") AS shipprice,
    CAST(COALESCE(JSON_VALUE(price_details, "$.shipping.price"), '0') AS FLOAT) AS shippricecoalfloat,
    COALESCE(CAST(JSON_VALUE(price_details, "$.shipping.price") AS FLOAT), 0) AS shippricefloatcoal
FROM `item`
WHERE order_id = 109517;

    Also, part of the "mess" with datatypes is caused by your JSON storing some values as strings with double quotes, and some as numeric values. I strongly recommend not enclosing numeric values in double quotes in your JSON.

    https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=d67fa297a5cc4248a06750d71581c022

    • added extra expression to show what happens if coalescing a float with an integer, vs a float with a float
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search