從Json陣列MSSQLServer中選擇資料

我必須像這樣從 Json 中選擇資料：

[
{
    "id": 10100,
    "externalId": "100000035",
    "name": "Test1",
    "companyId": 10099,
    "phone": "0738003811",
    "email": "[email protected]",
    "mainAddress": {
      "county": "UK",
      "province": "test",
      "zipCode": "01234",
      "city": "test",
      "street": "test",
      "gln": "44,37489331;26,21941193",
      "country": {
        "iso2": "UK",
        "iso3": "UK"
      }
    },
    "active": false,
    "main": true,
    "stores": [
      "Test"
    ],
    "attributes": [
      {
        "attributeId": 1059,
        "attributeName": "CH6 name",
        "attributeExternalId": null,
        "attributeValueId": 74292,
        "attributeValueType": "MONO_LINGUAL",
        "attributeValueEid": null,
        "attributePlainValue": "Unknown"
      },
      {
        "attributeId": 1061,
        "attributeName": "BD",
        "attributeExternalId": null,
        "attributeValueId": 81720,
        "attributeValueType": "MONO_LINGUAL",
        "attributeValueEid": null,
        "attributePlainValue": "Not assigned"
      }
 
    ],
    "daysSinceLastOrder": null
  },

   {
    "id": 62606,
    "externalId": "VL_LC_000190",
    "name": "Test",
    "companyId": 17793,
    "phone": "44333424",
    "email": "[email protected]",
    "mainAddress": {
      "firmName": "test",
      "county": "test",
      "province": "test",
      "zipCode": "247555",
      "city": "test",
      "street": "test",
      "gln": "44.8773851;23.9223518",
      "country": {
        "iso2": "RO",
        "iso3": "ROU"
      },
      "phone": "07547063789"
    },
    "active": true,
    "main": false,
    "stores": [
      "Valcea"
    ],
    "attributes": [
      {
        "attributeId": 1042,
        "attributeName": "Type of location",
        "attributeExternalId": "TYPE_OF_DIVISION",
        "attributeValueId": 34506,
        "attributeValueType": "MONO_LINGUAL",
        "attributeValueEid": "Small OTC (<40mp)",
        "attributePlainValue": "Small OTC (<40mp)"
      },
      {
        "attributeId": 17,
        "attributeName": "Limit for payment",
        "attributeExternalId": "LIMIT_FOR_PAYMENT_IN_DAYS",
        "attributeValueId": 59120,
        "attributeValueType": "NUMBER",
        "attributeValueEid": null,
        "attributePlainValue": "28"
      } 
    ],
    "daysSinceLastOrder": 147
  }
  ]
  

我知道如何使用“FROM OPENJSON”從簡單的 json 物件中選擇資料，但現在我必須為每個屬性選擇一個 AttributeValueId、AttributeId 和 AttributeName、attributePlainValue 和 CompanyId。所以我不知道如何從屬性陣列中選擇資料，然后如何加入這個上一級的 CompanyId。也許有人知道如何撰寫此查詢。

uj5u.com熱心網友回復：

例如，您可以使用這樣的代碼。

f1.metaData->"$.identity.customerID" = '.$customerID.'

uj5u.com熱心網友回復：

正如@lptr 在評論中提到的：

您需要將一個的結果傳遞OPENJSON給另一個，使用CROSS APPLY. 您可以使用語法選擇整個 JSON 物件或陣列作為屬性AS JSON

select
  t1.companyid,
  t2.*
from openjson(@j)
with (
  companyId int,
  attributes nvarchar(max) as json
) as t1
cross apply openjson(t1.attributes)
with
(
  attributeId int, 
  attributeName nvarchar(100),
  attributeValueId nvarchar(100),
  attributePlainValue nvarchar(100)
) as t2;

資料庫<>小提琴

標籤：sql json sql-server

上一篇：從API獲取資料后，如何在HTML表格中顯示api資料

下一篇：從JsonPath物件中提取資料時獲取空值