By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.6.7-MariaDB |
DROP TABLE IF EXISTS `item`;
CREATE TABLE `item` (
`item_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` int UNSIGNED NOT NULL DEFAULT '0',
`price_details` json DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
INSERT INTO `item` (
`item_id`,
`order_id`,
`price_details`
)
VALUES (
202733,
109517,
'{\"_lg21\": true, \"grid_id\": \"4060\", \"discount\": 0, \"gridStep\": 48, \"marginEt\": 0, \"shipping\": {\"price\": \"0.8648\", \"costPriceEt\": 0, \"priceIsFixed\": false}, \"commission\": 0.2, \"discountEt\": 0, \"domainPrice\": \"6.2500\", \"marginSupEt\": 0, \"sellingPrice\": 8.15, \"domainPriceEt\": \"6.2500\", \"isDomainPrice\": false, \"sellingPriceEt\": 6.792, \"customerGroupId\": \"5\", \"purchasePriceEt\": \"4.1700\", \"marginPercentage\": 0, \"commissionAmountEt\": 1.25, \"isSpecificGridForCustomer\": false, \"purchasePriceIncludeShipping\": true}'
);
SELECT
item_id,
JSON_EXTRACT(price_details, "$.shipping.price") AS shipprice,
CAST(COALESCE(JSON_EXTRACT(price_details, "$.shipping.price"), 0) AS varchar(100))
AS shippricecoalfloat,
COALESCE(CAST(JSON_EXTRACT(price_details, "$.shipping.price") AS FLOAT), 0)
AS shippricefloatcoal
, COALESCE(JSON_EXTRACT(price_details, "$.shipping.price"), 0) as coaaaaaaaaal
, CAST(JSON_EXTRACT(price_details, "$.shipping.price") AS FLOAT) as caaaaaaaaaaast
FROM `item`
WHERE order_id = 109517
| item_id | shipprice | shippricecoalfloat | shippricefloatcoal | coaaaaaaaaal | caaaaaaaaaaast |
|---|---|---|---|---|---|
| 202733 | "0.8648" | "0.8648" | 0.864799976348877 | "0.8648" | 0.8648 |
select cast('"0.8648"' as float)
| cast('"0.8648"' as float) |
|---|
| 0 |