代码之家 › 专栏 › 技术社区 › Randy Minder

从值的JSON数组中检索特定的JSON数组

sql-server-json sql-server json

Randy Minder · 技术社区 · 8 年前

我有一个SQL表,其中包含一列保存JSON数据。其中一个JSON值如下所示:

{
    "_id": "5a450f038104ca3cb0ff74b5",
    "index": 3,
    "guid": "20d807c5-bddc-44b9-97fe-fd18af1b6066",
    "isActive": false,
    "balance": "$2,832.38",
    "picture": "http://placehold.it/32x32",
    "age": 23,
    "eyeColor": "brown",
    "firstname": "Genevieve",
    "lastname": "Green",
    "gender": "female",
    "company": "PROFLEX",
    "email": "genevievegreen@proflex.com",
    "phone": "+1 (919) 464-2866",
    "address": "107 Clermont Avenue, Rew, California, 4298",
    "about": "Magna pariatur ut enim nulla pariatur ad Lorem amet. Proident nulla exercitation id Lorem commodo minim cillum irure exercitation labore nostrud nostrud sint. Voluptate commodo ea commodo quis Lorem laborum culpa voluptate enim nulla enim duis.\r\n",
    "registered": "2016-02-16T09:51:25 +05:00",
    "latitude": -16.492643,
    "longitude": -71.782118,
    "tags": [
      "in",
      "non",
      "eiusmod",
      "labore",
      "dolor",
      "laboris",
      "ullamco"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Mccoy Berg",
        "interests": [
          "Music",
          "Birding",
          "Chess"
        ]
      },
      {
        "id": 1,
        "name": "Chase Mcfadden",
        "interests": [
          "Software",
          "Chess",
          "History"
        ]
      },
      {
        "id": 2,
        "name": "Michele Dodson",
        "interests": [
          "Football",
          "Birding",
          "Movies"
        ]
      }
    ],
    "greeting": "Hello, Genevieve! You have 2 unread messages.",
    "favoriteFruit": "strawberry"
  }

我可以执行一个查询,检索名字和姓氏以及所有朋友,如下所示:

SELECT
  JSON_VALUE(JsonValue, '$.firstname') as FirstName,
  JSON_VALUE(JsonValue, '$.lastname') as LastName,
  JSON_QUERY(JsonValue, '$.friends') as FriendsList,
From <MyTable>
Where JSON_VALUE(JsonValue,'$.lastname') = 'Green'

正如所写的那样,查询返回FriendsList的JSON字符串,如下所示:

[  
   {  
      "id":0,
      "name":"Mccoy Berg",
      "interests":[  
         "Music",
         "Birding",
         "Chess"
      ]
   },
   {  
      "id":1,
      "name":"Chase Mcfadden",
      "interests":[  
         "Software",
         "Chess",
         "History"
      ]
   },
   {  
      "id":2,
      "name":"Michele Dodson",
      "interests":[  
         "Football",
         "Birding",
         "Movies"
      ]
   }
]

实际上,我想要的只是一组朋友的名字,类似这样:

["Mccoy Berg", "Chase Mcfadden", ...]

我相信这是可能的,但我对JSON的了解有限。

1 回复 | 直到 8 年前

Martin Smith 8 年前

SQL server在以您期望的格式创建数组时有点可笑。默认情况下,它总是将JSON数组创建为键:值对。您可以通过使用 STUFF() 和 FOR XML 使用JSON时。

首先创建只返回名称的子查询,然后可以 STUFF 这些名字进入 FriendsList 字段,如下所示:

SELECT
FirstName    = JSON_VALUE(JsonValue, '$.firstname')
,LastName    = JSON_VALUE(JsonValue, '$.lastname')
,FriendsList = '[' + STUFF(
                        (SELECT ',' + '"' + [name] + '"'
                            FROM OPENJSON(JsonValue,'$.friends')
                            WITH ( [name] NVARCHAR(100) '$.name') 
                        FOR XML PATH (''))
                        , 1, 1, '') 
            + ']'
FROM <MyTable>