时间戳

首页 > 解决方案 > SQL server OPENJSON，显示 JSON 原始字符串和字段的最佳方法

sql - SQL server OPENJSON，显示 JSON 原始字符串和字段的最佳方法

问题描述

我正在尝试使用OPENJSON()TSQL 从 JSON 文件中选择结果：

ask_price,  bid_price,  mark_price  ,high_price ,low_price  ,open_price ,KEY ,value

我能够获取这些值，但它包含重复项。它应该只返回 3 行，但如您所见，它是 9 行。

我尝试使用 distinct 和 group by 但仍然有重复，它可能会影响性能。有没有我可以使用的替代方案？

DECLARE @JSONData AS NVARCHAR(4000) 

SET @JSONData = N'{ "results":[ { "ask_price":"377.360000", "open_price":"376.475000","symbol":"BCHUSD","id":"24566"},
      { "ask_price":"9668.090000","open_price":"9642.645000","symbol":"BTCUSD","id":"32555"},
      {"ask_price":"262.660000", "open_price":"260.890000","symbol":"ETHUSD","id":"356789"} ] }'      

SELECT Y.*, Z.[KEY],Z.[value]  FROM OPENJSON(@JSONData) AS x CROSS APPLY OPENJSON(x.value) WITH ( ask_price numeric(18,12), open_price numeric(18,12) ) AS Y CROSS APPLY OPENJSON(@JSONData, '$.results') as Z

结果

Current results 

ask_price   open_price  KEY value
377.360000000000    376.475000000000    0   { "ask_price":"377.360000", "open_price":"376.475000","symbol":"BCHUSD","id":"24566"}
377.360000000000    376.475000000000    1   { "ask_price":"9668.090000","open_price":"9642.645000","symbol":"BTCUSD","id":"32555"}
377.360000000000    376.475000000000    2   {"ask_price":"262.660000", "open_price":"260.890000","symbol":"ETHUSD","id":"356789"}
9668.090000000000   9642.645000000000   0   { "ask_price":"377.360000", "open_price":"376.475000","symbol":"BCHUSD","id":"24566"}
9668.090000000000   9642.645000000000   1   { "ask_price":"9668.090000","open_price":"9642.645000","symbol":"BTCUSD","id":"32555"}
9668.090000000000   9642.645000000000   2   {"ask_price":"262.660000", "open_price":"260.890000","symbol":"ETHUSD","id":"356789"}
262.660000000000    260.890000000000    0   { "ask_price":"377.360000", "open_price":"376.475000","symbol":"BCHUSD","id":"24566"}
262.660000000000    260.890000000000    1   { "ask_price":"9668.090000","open_price":"9642.645000","symbol":"BTCUSD","id":"32555"}
262.660000000000    260.890000000000    2   {"ask_price":"262.660000", "open_price":"260.890000","symbol":"ETHUSD","id":"356789"}


Expected results 

    ask_price   open_price  KEY value
377.360000000000    376.475000000000    0   { "ask_price":"377.360000", "open_price":"376.475000","symbol":"BCHUSD","id":"24566"}
9668.090000000000   9642.645000000000   1   { "ask_price":"9668.090000","open_price":"9642.645000","symbol":"BTCUSD","id":"32555"}
262.660000000000    260.890000000000    2   {"ask_price":"262.660000", "open_price":"260.890000","symbol":"ETHUSD","id":"356789"}

标签： sqljsonsql-servertsqlstored-procedures

解决方案

以下查询可以解决问题：

SELECT resdata.*, res.[KEY], res.[Value] FROM OPENJSON(@JSONData) AS resTab 
CROSS APPLY OPENJSON(resTab.value) AS res
CROSS APPLY OPENJSON(res.value)
WITH ( ask_price numeric(18,12), bid_price numeric(18,12),  mark_price numeric(18,12),  
        high_price numeric(18,12),   low_price numeric(18,12),  open_price numeric(18,12) ) AS resdata

推荐阅读