SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.YourTable (YourJSON nvarchar(MAX));
INSERT INTO dbo.YourTable(YourJSON)
VALUES
(N'{"distributionOrders":{"3000283984":[{"orderNumber":"3000283984","orderType":"STC","itemNumber":"W01874"}]}}'),
(N'{"distributionOrders":{"3000308956":[{"orderNumber":"3000308956","orderType":"EVA","itemNumber":"S28741"}]}}'),
(N'{"distributionOrders":{"3000308961":[{"orderNumber":"3000308961","orderType":"EXP","itemNumber":"W09234"}]}}'),
(N'{"distributionOrders":{"3000309119":[{"orderNumber":"3000309119","orderType":"STC","itemNumber":"W01874"}]}}');
4 rows affected

SELECT dO.orderNumber,
       dO.orderType,
       dO.itemNumber
FROM dbo.YourTable YT
     CROSS APPLY OPENJSON(YT.YourJSON, '$.distributionOrders') J
     CROSS APPLY OPENJSON(J.[value])
                 WITH (orderNumber bigint,
                       orderType varchar(3),
                       itemNumber varchar(6)) dO;


orderNumber
orderType
itemNumber


3000283984
STC
W01874

3000308956
EVA
S28741

3000308961
EXP
W09234

3000309119
STC
W01874


DROP TABLE dbo.YourTable;

orderNumber	orderType	itemNumber
3000283984	STC	W01874
3000308956	EVA	S28741
3000308961	EXP	W09234
3000309119	STC	W01874