MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.6.8-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_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_INT,
	COALESCE(CAST(JSON_VALUE(price_details, "$.shipping.price") AS FLOAT), CAST(0 AS FLOAT)) AS shippricefloatcoal_FLOAT
FROM `item`
WHERE order_id = 109517;


item_id
shipprice
shippricecoalfloat
shippricefloatcoal_INT
shippricefloatcoal_FLOAT


202733
0.8648
0.8648
0.864799976348877
0.8648