MariaDB 10.6

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