选择所有json属性 [英] select all json attributes

问题描述

我的样本json是

{

 """""" Short"，

 " entryprice"：122.618，

 " exitprice"：122.556，

 " ; potentialtarget"：0.061309316370273863，

 " entrytime"：" 2016-05-23T08：22：59.1636488-04：00" ;，

 " exittime"：" 2016-05-23T10：35：44.5076809-04：00"，

 " maxfavourable"：0.23，

 " maxagainst"：-0.078，

 """" EnterShortBounce"， < br style ="">
 " instrument"：" EURJPY"

}

{
 "direction": "Short",
 "entryprice": 122.618,
 "exitprice": 122.556,
 "potentialtarget": 0.061309316370273863,
 "entrytime": "2016-05-23T08:22:59.1636488-04:00",
 "exittime": "2016-05-23T10:35:44.5076809-04:00",
 "maxfavourable": 0.23,
 "maxagainst": -0.078,
 "signal": "EnterShortBounce",
 "instrument": "EURJPY"
}

我现在可以通过使用$ .direction或$ .entryprice识别它来选择json值for ex

I can now select json values by identifying it using the $.direction or $.entryprice for ex

SELECT TOP 1000 [_id]，JSON_VALUE（交易，'$。direction'）

  FROM [dbTorontoTrader2016]。[dbo]。[交易]

SELECT TOP 1000 [_id], JSON_VALUE(trade,'$.direction')
  FROM [dbTorontoTrader2016].[dbo].[Trades]

如何选择json的所有属性而不是$ .direction，$。 entryprice？是否有等效的select *功能？

how do I select all the attributes of the json instead of using $.direction, $.entryprice? Is there a equivalent select * functionality?

推荐答案

美好的一天，

>>  如何选择所有属性json

>> how do I select all the attributes of the json

JSON没有属性面向文档但是
面向数据（不像面向文档的XML。 JSON基于：
对象，数组，以及  值。  JSON格式化为键对：。 值可以是对象，数组或简单值。

JSON does not have attributes it is not document oriented but data oriented (not like XML which is document oriented). JSON based on: Objects, Arrays, and Values. JSON formatted as pairs of Key : Value. Value can be Object, Array, or simple value.

*我强烈建议您下载以下文件链接。它包括代码文件和SQLSaturday＃481事件的演示文稿。检查演示文稿文件（pdf）幻灯片7-9

https://gallery.technet.microsoft.com/Built-in-JSON-support-in-060c73ce

* I HIGHLY recommend you to download the files in the following link. It's includes code files and the presentation from SQLSaturday#481 event. Check the presentation file (pdf) slides 7-9
https://gallery.technet.microsoft.com/Built-in-JSON-support-in-060c73ce

>>  是否有等效的select *功能？

>> Is there a equivalent select * functionality?

如果我理解正确（我不确定） ），那么你的问题是你有几个具有相同键名的元素（在同一级别），并且你想获得它们的所有值。一个简单的解决方案是使用OPENJSON将JSON转换为表
格式，并返回具有特定键名的所有值，例如：

If I understand you correctly (I am not sure), then your issue is that you have several elements with the same key name (in the same level), and you want to get all their values. A simple solution will be to use OPENJSON in order to convert the JSON to table format, and to return all the values with specific key name for example:

declare @JSON nvarchar(MAX) = 
'{
 "direction": "Short",
 "direction": "Short2",
 "entryprice": 122.618,
 "exitprice": 122.556,
 "potentialtarget": 0.061309316370273863,
 "entrytime": "2016-05-23T08:22:59.1636488-04:00",
 "exittime": "2016-05-23T10:35:44.5076809-04:00",
 "maxfavourable": 0.23,
 "maxagainst": -0.078,
 "signal": "EnterShortBounce",
 "instrument": "EURJPY"
}' 
select @JSON
select [value] 
FROM OPENJSON( @JSON, '

'）
其中[key] ='direction'
GO

') where [key] = 'direction' GO

*如果您移除过滤器 其中[key] ='direction'，那么您将获得所有值该级别（在此示例中，所有值都在顶级）。如果您有层次结构数据，那么您可以使用CROSS APPLY进入下一级别并获得
所有值

* If you remove the filter where [key] = 'direction', then you will get all the values in that level (in this example all values are in the top level). If you have hierarchy data then you can use CROSS APPLY in order to move to the next level and get all the values

**如果这不是您要查找的内容，然后请在样本

** If this is not what you look for, then please post the desired result given the sample

这篇关于选择所有json属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！