在Server 2017中解析JSON (Clearbit API调用)

Question

我通过Clearbit提供者使用API调用将一些数据拖到本地服务器上的数据库中。关于使用Server 2017解析数据，一切都很顺利，直到我遇到了麻烦。

为了更容易理解，我将直截了当地举这个例子。

这是JSON中API调用输出的示例

{
  "id": "384dfe0d-5bba-445e-a390-2d946dc84a12",
  "name": "Honeywell",
  "legalName": "Honeywell International Inc",
  "domain": "honeywell.com",
  "domainAliases": [
    "honeywell.at",
    "honeywell.it",
    "evohome.info",
    "wifithermostat.com",
    "emsaviation.com",
    "mytotalconnect.com",
    "honeywell.nl",
    "honeywell.co.za",
    "honeywell.com.au",
    "honeywell.ca",
    "alliedsignal.com",
    "emsdss.com",
    "primusepic.com",
    "alarmnet-me.com",
    "lebow.com",
    "honeywell.ie",
    "honeywell.jp",
    "honeywell.com.br",
    "trendcontrol.co.uk",
    "honeywellforjaguar.co.uk",
    "aviaso.com",
    "skyforce.co.uk",
    "newenglandinstruments.com",
    "honeywell.fi",
    "alarmnet.com",
    "skyconnect.com",
    "skyforceuk.com",
    "securitex.com",
    "missionready.com",
    "honeywellaerospace.com",
    "formation.com",
    "aclon.com",
    "electrocorp.com",
    "ultrak.com",
    "satcom1.com",
    "hsmpats.com",
    "myaerospace.com",
    "emsglobaltracking.com",
    "fascocontrols.com",
    "honeywellnow.com",
    "bendixbrakes.com",
    "elmwoodsensors.com",
    "ovationselect.com",
    "honeywellbusinessaviation.com",
    "iflyaspire.com",
    "btrinc.com",
    "honeywellspecialtymaterials.com",
    "magneticsensors.com",
    "activeye.com",
    "egarrett.com",
    "novar-eds.com",
    "aviaso.co.uk",
    "chadwick-helmuth.com",
    "datainstruments.com",
    "lebowproducts.com",
    "honeywell-produktkatalog.de",
    "honeywellforjaguar.com",
    "hobbs-corp.com",
    "emsgt.com",
    "honeywellaes.com",
    "honeywellbuildingsolutions.com",
    "satcom1.aero",
    "honeywell-building-solutions.de",
    "lifesafetydistribution.com",
    "godirect.com",
    "garrettbulletin.com",
    "yourhomeexpert.com",
    "aerospacetrading.com",
    "sensorsystems.com",
    "wifithermostat.info",
    "honeywell-fachseminare.de",
    "hobbscorporation.com",
    "kcl.hu",
    "honeywell.sk",
    "esser.info",
    "inertialsensor.com",
    "sensotec.com",
    "notifier.com",
    "honeywellgreer.com",
    "smartact.de",
    "honeywellfire.com",
    "iris-systems.com",
    "honeywell.ru",
    "lxei.com",
    "thermalswitch.com",
    "hightempsolutions.com",
    "aubetech.com",
    "honeywell-haustechnik.de",
    "careersathoneywell.com",
    "garrettbyhoneywell.com",
    "honeywell.in",
    "honeywell.cn",
    "honeywell.com.mx",
    "kcp.com",
    "satamatics.com",
    "myflite.com"
  ],
  "site": {
    "title": "Honeywell",
    "h1": null,
    "metaDescription": " We are blending products with software solutions to link people and businesses to the information they need to be more efficient, safer and connected. ",
    "metaAuthor": null,
    "phoneNumbers": [
      "+1 877-271-8620",
      "+1 800-633-3991",
      "+1 877-841-2840",
      "+1 480-353-3020",
      "+1 973-455-3388",
      "+1 973-204-9621",
      "+32 2 728 20 45",
      "+32 476 20 90 19",
      "+44 7794 007289",
      "+86 21 2219 6509"
    ],
    "emailAddresses": [
      "domains@honeywell.com",
      "HoneywellPrivacy@honeywell.com",
      "rob.ferris@honeywell.com",
      "ilse.schouteden@honeywell.com",
      "chris.martin2@honeywell.com",
      "Anahi.Espinosa@honeywell.com",
      "lydia.lu@honeywell.com",
      "madhavi.jha@Honeywell.com",
      "Steven.Brecken@Honeywell.com",
      "Steve.Brecken@Honeywell.com",
      "Eugene.Tan@Honeywell.com"
    ]
  },
  "category": {
    "sector": "Consumer Discretionary",
    "industryGroup": "Automobiles & Components",
    "industry": "Automotive",
    "subIndustry": "Automotive",
    "sicCode": "3714",
    "naicsCode": null
  },
  "tags": [
    "Automotive",
    "Enterprise",
    "B2B",
    "Electrical"
  ],
  "description": " We are blending products with software solutions to link people and businesses to the information they need to be more efficient, safer and connected. ",
  "foundedYear": 1936,
  "location": "115 Tabor Rd, Morris Plains, NJ 07950, USA",
  "timeZone": "America/New_York",
  "utcOffset": -4,
  "geo": {
    "streetNumber": "115",
    "streetName": "Tabor Road",
    "subPremise": null,
    "city": "Morris Plains",
    "postalCode": "07950",
    "state": "New Jersey",
    "stateCode": "NJ",
    "country": "United States",
    "countryCode": "US",
    "lat": 40.8358456,
    "lng": -74.4771042
  },
  "logo": "https://logo.clearbit.com/honeywell.com",
  "facebook": {
    "handle": "293855263965203",
    "likes": null
  },
  "linkedin": {
    "handle": "company/honeywell"
  },
  "twitter": {
    "handle": "HoneywellNow",
    "id": "257492733",
    "bio": "Please visit us over at @Honeywell.",
    "followers": 2322,
    "following": 1,
    "location": "Morris Plains, NJ",
    "site": "https:",
    "avatar": 
  },
  "crunchbase": {
    "handle": "organization/honeywell"
  },
  "emailProvider": false,
  "type": "public",
  "ticker": "HON",
  "phone": "+1 973-455-2000",
  "metrics": {
    "alexaUsRank": 6045,
    "alexaGlobalRank": 18053,
    "googleRank": null,
    "employees": 51779,
    "employeesRange": "1000+",
    "marketCap": 102920000000,
    "raised": null,
    "annualRevenue": 39302000000,
    "fiscalYearEnd": 12
  },
  "indexedAt": "2017-07-11T23:00:41.115Z",
  "tech": [
    "crazy_egg",
    "google_analytics",
    "google_tag_manager",
    "asp_net",
    "mouseflow",
    "marketo",
    "go_squared",
    "microsoft_exchange_online",
    "outlook",
    "recaptcha"
  ],
  "parent": {
    "domain": null
  },
  "similarDomains": [
    "abb-livingspace.com",
    "alerton.com",
    "gereports.com",
    "honeywellprocess.com",
    "honeywelluk.com",
    "johnsoncontrols.com",
    "jpinstruments.com",
    "lenel.com",
    "maxitrol.com",
    "nucalgon.com",
    "schneider-electric.us",
    "siemens.com"
  ]
}

如果您查看上面的示例，您将看到"domainAliases": [...]，这是我仍然需要解析的JSON的一部分。

这是我已经拥有的SQL的解析查询：

SELECT * 
     , JSON_VALUE(JSONData,'$.name') AS CompanyName
     , JSON_VALUE(JSONData,'$.category.sector') AS CategorySector
     , JSON_VALUE(JSONData, '$.category.industryGroup') AS CategoryIndustryGroup
     , JSON_VALUE(JSONData, '$.category.industry') AS CategoryIndustry
     , JSON_VALUE(JSONData, '$.category.subIndustry') AS CategorySubIndustry
     , JSON_VALUE(JSONData, '$.category.sicCode') AS CategorySicCode
     , JSON_VALUE(JSONData, '$.category.naicsCode') AS CategoryNaicsCode
     , JSON_VALUE(JSONData, '$.metrics.employees') AS EmployeesNumber
     , JSON_VALUE(JSONData, '$.metrics.employeesRange') AS EmployeesRange
     , JSON_VALUE(JSONData, '$.metrics.marketCap') AS MarketCap
     , JSON_VALUE(JSONData, '$.metrics.annualRevenue') AS AnnualRevenue
     , JSON_VALUE(JSONData, '$.similarDomains') AS SimilarDomains
FROM Domains;

我希望将这个数据("domainAliases")作为上层查询中的数据存储在其他表中(我知道我已经拥有的解析查询只是一个SELECT查询，但我也有一个查询的更新版本)。

下面是一个示例图片，说明新表__中的成品应该是什么样子的。左列称为Company Name__，第二列称为Domain Aliases__：

​

​

现在，是存储JSON数据的地方吗？我将它存储在一个名为JSONData的列中，即tablename：Domains，所有这些都存储在一个名为Domainbank的数据库中。JSONData数据类型为nvarchar(max)。

我需要按公司名称分组数据，在公司名称旁边应该有别名域，就像图片示例所显示的那样。现在，请记住，我将运行对10k+ JSONData的查询，将要创建的新表将非常庞大，但是只要所有这些表都是按照公司名称分组的，所有别名域都应该是好的。一些JSONData没有以正确的格式返回API调用，因为它们要么找不到数据，要么其他东西出了问题，所以如果查询在"domainAliases": [...]下找不到任何内容，或者它甚至找不到"domainAliases": [...]，那么我不需要公司出现在新的表上。

简单地回顾一下:让我们创建一个新的表(让我们称之为AliasDomains)，在"domainAliases": [...]下查找数据--同时提取公司名称JSON_VALUE(JSONData,'$.name') AS CompanyName，将数据存储在新表中，作为文章中较高的图片示例，然后按CompanyName分组。

Answer 1

因此，从您的帖子中，我不完全清楚您的问题是什么，但我假设它是如何编写一些SQL语句来完成上述工作？

首先，我想说的是，在从表中检索数据时，不应该关心插入中的GROUP BY，执行GROUP BY。

尽管如此，您可以很容易地使用来自SELECT表的Domains和CROSS APPLY OPENJSON语句来完成所需的任务，如下所示：

INSERT INTO AliasDomains(CompanyName, DomainAliases)
SELECT JSON_VALUE(JSONData, '$.name'), value 
FROM Domains
CROSS APPLY OPENJSON (JSONData, '$.domainAliases')

编辑：可能会添加上述语句中的value是从OPENJSON返回的，例如，它引用了您想要的路径(在本例中是domainAliases)的值。

希望这能帮上忙？！

尼尔斯