将json数组提取到SQL中的列

Question

我有以下JSON集合表:

SliderJson3[{"Slider" 11日"Value": 4},{"Slider" 4,"Value": 3},{"Slider" 25岁的"Value": 3},{"Slider" 2,"Value": 4},{"Slider" 5"Value": 3}]

我不确定您为什么认为需要所有这些游标和临时表，它们是不必要的。

这里需要两个OPENJSON。要获得每个滑块的值，您只需旋转滑块编号即可。

SELECT
s.*,
j.*
FROM dbo.SmallData s
CROSS APPLY (
SELECT
pvt.*
FROM (
SELECT
j2.Slider,
j2.Value
FROM OPENJSON (s.SliderJSON) j1
CROSS APPLY OPENJSON (j1.value)
WITH (Slider int, Value int) j2
) j
PIVOT (
MIN(Value) FOR Slider IN
([4],[5])
) pvt
) j;

第二个版本也可以非常简单地使用多列枢轴.

SELECT
s.*,
j.*
FROM dbo.SmallData s
CROSS APPLY (
SELECT
MAX(CASE WHEN j1.[key] = '0' THEN j2.Slider END) slider1,
MAX(CASE WHEN j1.[key] = '0' THEN j2.Value  END)  value1,
MAX(CASE WHEN j1.[key] = '1' THEN j2.Slider END) slider2,
MAX(CASE WHEN j1.[key] = '1' THEN j2.Value  END)  value2,
MAX(CASE WHEN j1.[key] = '2' THEN j2.Slider END) slider3,
MAX(CASE WHEN j1.[key] = '2' THEN j2.Value  END)  value3,
MAX(CASE WHEN j1.[key] = '3' THEN j2.Slider END) slider4,
MAX(CASE WHEN j1.[key] = '3' THEN j2.Value  END)  value4,
MAX(CASE WHEN j1.[key] = '4' THEN j2.Slider END) slider5,
MAX(CASE WHEN j1.[key] = '4' THEN j2.Value  END)  value5,
MAX(CASE WHEN j1.[key] = '5' THEN j2.Slider END) slider6,
MAX(CASE WHEN j1.[key] = '5' THEN j2.Value  END)  value6,
MAX(CASE WHEN j1.[key] = '6' THEN j2.Slider END) slider7,
MAX(CASE WHEN j1.[key] = '6' THEN j2.Value  END)  value7,
MAX(CASE WHEN j1.[key] = '7' THEN j2.Slider END) slider8,
MAX(CASE WHEN j1.[key] = '7' THEN j2.Value  END)  value8,
MAX(CASE WHEN j1.[key] = '8' THEN j2.Slider END) slider9,
MAX(CASE WHEN j1.[key] = '8' THEN j2.Value  END)  value9
FROM OPENJSON (s.SliderJSON) j1
CROSS APPLY OPENJSON (j1.value)
WITH (Slider int, Value int) j2
) j;