选择json值该匹配列ID

Question

我的数据是付款，金额，货币和JSON对象，该对象具有该特定日期的汇率。该JSON包含所有货币和所有汇率。

金额	货币	汇率
666.00	USD	[{“ basecurrency”：“ sek”，“ ExchangeCurrency”：“ GBP”，“费率”：0.07848，“日期”：2018-10-21}，{“ Basecrency” “ ExchangeCurrency”：“ USD”，“ RATE”：0.106593，“日期”：2018-10-21]
123.00	GBP	[{“ basecurrency”：“ sek”，“ offerceNageCurrency”：“ fivernangecurrency”：“ “日期”：2020-03-10}，{“ basecurrency”：“ sek”，“ fiffangecurrency”：“ usd”，“速率”：0.117549，“日期”：2020-03-10}

]每行的每个对象中的正确速率？最终目标是将一列称为“ sek中的数量”。

我希望结果看起来像这样：

金额	货币	汇率	汇率	美元
666.00	[{{“ basecurrency”：“ sek”，“ fiffingangecurrency”：“ GBP	”，“速率”：0.07848，“日期”：2018-- “ usd”，“速率”：0.106593，“日期”：2018-10-21}]	0.106593	6248.07
123.00	10-21}，{“ basecurrency”：“ sek”，“ fiffangecurrency ”	： sek“，“ fiffangecurrency”：“ GBP”，“速率”：0.08239，“日期”：2020-03-10}，{“ basecurrency”：“ sek”，“ sek''，“ fivferangecurrency”：“ usd”：“ usd”，“ rate”，“ rate”：0.117549 ，“日期”：2020-03-10}]	0.08239	1492.90

Answer 1

如果使用SQL Server 2016+，则可以使用OpenJson（）来解析JSON数据。以下语句是解决问题的可能解决方案（不舍入）：

示例数据：

SELECT *
INTO Data
FROM (VALUES
   (666.00, 'USD', '[{"BaseCurrency":"SEK","ExchangeCurrency":"GBP","Rate":0.07848,"Date":"2018-10-21"},{"BaseCurrency":"SEK","ExchangeCurrency":"USD","Rate":0.106593,"Date":"2018-10-21"}]'),
   (123.00, 'GBP', '[{"BaseCurrency":"SEK","ExchangeCurrency":"GBP","Rate":0.08239,"Date":"2020-03-10"},{"BaseCurrency":"SEK","ExchangeCurrency":"USD","Rate":0.117549,"Date":"2020-03-10"}]')
) v ([Amount], [Currency], [Exchange rates])

statment：

SELECT 
   d.[Amount], d.[Currency], d.[Exchange rates], 
   j.[Rate], 
   CASE WHEN j.[Rate] > 0.0 THEN d.[Amount] / j.[Rate] END AS [Amount in SEK]
FROM Data d
OUTER APPLY OPENJSON(d.[Exchange rates]) WITH (
   ExchangeCurrency varchar(3) '$.ExchangeCurrency',
   Rate numeric(20, 5) '$.Rate'
) j 
WHERE d.Currency = j.ExchangeCurrency

结果：

666.00	汇率	汇率	金额	[
usd	货币	{“ basecurrency”：“ sek”，“ exchangeCurrency”，“ sekangecurrency”：“ “速率”：0.07848，“ date”：“ 2018-10-21”}，{“ basecurrency”：“ sek”，“ fivferangecurrency”：“ usd”，“速率”：0.106593，“日期”：“ 2018-10 -21“}]	0.10659	6248.24092316316352378278271882915
123.00	GBP	[{“ basecurrency”：“ sek”，“ sek”，“ fiffergecurrency”：“ ：“ sek”，“ fiffangecurrency”：“ USD”，“速率”：0.117549，“日期”：“ 2020-03-10”}]	0.08239	1492.89996237407452360723333887

Answer 2

谢谢，这似乎是我想要的，我已经做了一些研究并尝试了自己的代码，但似乎行不通。我还仔细检查了服务器版本。我正在运行SQL Server 13.0.6300.2 Google告诉我的是2016年。

我将数据加载到示例临时表中。

SELECT top 100
s.STM_AMOUNT [Amount],
d.DIC_CODE [Currency],
REPLACE([CRR_CURRENCIES],'''','"') as [Exchange rates]
into #data
FROM database.SETTLEMENTS s
left join database.CURRENCIES c on c.crr_id = s.stm_crr_id 
left join database.DICTIONARIES d on d.dic_id = s.stm_cur_dic_id

结果

select * from #data

我进行您建议的代码：

Select 
d.[Amount], d.[Currency], d.[Exchange rates], 
j.[Rate],
CASE WHEN j.[Rate] > 0.0 THEN d.[Amount] / j.[Rate] END AS [Amount in SEK]
from #data d

OUTER APPLY OPENJSON(d.[Exchange rates])
WITH(
ExchangeCurrency varchar(3) '$.ExchangeCurrency',
Rate numeric(20, 5) '$.Rate'
) j 
WHERE d.Currency = j.ExchangeCurrency 

我也尝试了“ Cross Apply”，但我获得了此错误代码：
不正确的语法靠近关键字“ with with”。如果此语句是一个常见的表表达式，则XMLNAMESPACES子句或更改跟踪上下文子句，

则 必须用一个半词来终止先前的语句。结肠出错后。