{"id":75102,"date":"2017-10-27T14:24:32","date_gmt":"2017-10-27T14:24:32","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=75102"},"modified":"2026-04-14T13:00:40","modified_gmt":"2026-04-14T13:00:40","slug":"importing-json-web-services-applications-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/importing-json-web-services-applications-sql-server\/","title":{"rendered":"Import and Validate JSON from Web Services into SQL Server: T-SQL Techniques"},"content":{"rendered":"

Before importing JSON data from a web service or application into SQL Server, it is worth validating it in T-SQL – checking that it has the expected path structure, that all required properties are present, that values are the right SQL data types, and that they pass any relevant constraint checks. SQL Server’s JSON functions (JSON_VALUE, OPENJSON, ISJSON) provide the tools to build this validation pipeline entirely in T-SQL, without external schema validators. This article covers the validation steps in sequence: path expression parsing, metadata comparison, data type checking, constraint validation, and finally a pattern for scaling validation to large JSON payloads via temporary tables.<\/p>\n

Articles by Phil Factor about JSON and SQL Server:<\/h4>\n
    \n
  1. Consuming JSON Strings in SQL Server (Nov 2012)<\/a> <\/li>\n
  2. SQL Server JSON to Table and Table to JSON (March 2013)<\/a> <\/li>\n
  3. Producing JSON Documents From SQL Server Queries via TSQL (May 2014)<\/a> <\/li>\n
  4. Consuming hierarchical JSON documents in SQL Server using OpenJSON (Sept 2017)<\/a> <\/li>\n
  5. Importing JSON data from Web Services and Applications into SQL Server(October 2017)<\/a> <\/li>\n<\/ol>\n\n

    There are a number of reasons why database developers or DBAs will regard JSON with suspicion. They are used to the protection of a higher level of constraints and checks for imported data: They want to be sure that the metadata of the data is correct before they import it: They also want to check the data itself. At the simplest level, they want to be confident that a regular feed from a data source is as robust as possible. Why so fussy? Simply because once bad data gets into a database of any size, it is tiresome and time-consuming to extract it.<\/p>\n

    As well as taking JSON data from web-based applications, especially single-page ones, many databases rely on volatile data from web services, such as a list of currencies and their current value against the dollar. We need to be sure that each time it happens, the JSON has the same metadata or data structure as usual, and that the data is valid. Although nowadays, application data generally comes as a JSON document, we have a similar problem with other types of document-based data: XML, for example, exists in two variations: Schema-based or typed XML which is good and virtuous but has a naughty sister, schema-less or untyped XML. Typed XML is the only sort of XML you should allow in a SQL Server database. Not only is it safer to use, but it is stored much more efficiently. Untyped XML, JSON and YAML all require checks.
    We\u2019d like to do the same with JSON as we do with typed XML, and allow JSON Schema to do the donkey work; but, sadly, SQL Server don’t currently support any JSON schema binding and cannot, therefore, store JSON efficiently in a \u2018compiled\u2019 form. (note: you can\u00a0get     25x compression of JSON data, and far better performance by using clustered columnstore indexes<\/a>, but that is a different story)\u00a0<\/p>\n

    The dominant JSON Schema is actually called \u2018JSON Schema\u2019 and is in IETF draft 6. It can describe a data format in human-readable JSON and can be used to provide a complete structural validation. PostgreSQL has postgres-json-schema<\/strong> for validating JSON, JavaScript has many add-ins for doing this. Net programmers have Json.NET Schema and several other alternatives.<\/p>\n

    PowerShell is the obvious place to validate your JSON via JSON.net schema, but there is, in fact, quite a lot you can do in SQL to check your JSON data. You can easily compare the metadata of two JSON documents to see if anything has changed, and you can apply constraints on JSON Data. This is because of the use of path expressions. To get started we\u2019d better describe them, because they are essential to any serious use of JSON in SQL Server.<\/p>\n

    Path expressions<\/h1>\n

    When you need to read from, or write to, values from a JSON document in SQL Server, you use JSON path expressions. These can reference objects, arrays or values.<\/p>\n

    These path expressions are needed if you call OPENJSON with the WITH clause to return a relational table-source, if you call JSON_VALUE to extract a single value from JSON text, or when you call JSON_MODIFY to update this value or append to it.<\/p>\n

    The path itself starts with a dollar sign ($) that represents the context item. The path consists of elements separated by \u2018dots\u2019 or full-stops.<\/p>\n

    The property path is a set of path steps that consist of Key names that are optionally \u2018quoted\u2019. (e.g. $.info.address.county<\/strong> or $.info.address.\u201dpost code\u201d.outward<\/strong>). Each dot denotes that the Lvalue (left-side value) is the parent of the RValue (right-side value). If the key name starts with a dollar sign or contains special characters such as spaces then you need to use double-quoted delimiters for the key (e.g. $.info.Cost.”La Posche Hotel”<\/strong>). If the key name refers to an array, they are indexed with a zero-based index. (e.g. $.info.tags[0]<\/strong> or $.info.tags[1]<\/strong>)<\/p>\n

    If the path references an object that exists more than once, neither JSON_Value <\/strong>nor JSON_modify<\/strong> can access the second or subsequent values. In this case, you have to use OpenJSON<\/strong> instead to get to all the values.<\/p>\n

    JSON paths in SQL Server can start with a keyword \u2018lax\u2019 or \u2018strict\u2019. It is an unusual requirement to want to suppress errors if a path isn\u2019t found in the JSON document but this is the default, and you can specify this by using \u2018lax\u2019. You\u2019d be more likely to want \u2018strict\u2019 mode, but \u2018lax\u2019 is better if you want to test whether a path value is there because you will know something is wrong by the NULL return value<\/p>\n

    You can easily use path expressions. The first function you\u2019d probably need is a routine to tell you what these expressions actually are for any particular JSON document.<\/p>\n

    Finding out what paths there are in a JSON string or document<\/h1>\n

    Here is a routine that takes a JSON string and returns a table-source containing the expressions, data types and values for the JSON that you specify.<\/p>\n

    IF Object_Id('dbo.JSONPathsAndValues') IS NOT NULL DROP FUNCTION dbo.JSONPathsAndValues;\n  GO\n  CREATE FUNCTION dbo.JSONPathsAndValues\n    \/**\n    Summary: >\n     This function takes a JSON string and returns\n    a table containing the JSON paths to the data,\n    and the data itself. The JSON paths are compatible with  OPENjson, \n    JSON_Value and JSON_Modify. \n    Author: PhilFactor\n    Date: 06\/10\/2017\n    Version: 2 \n    Database: PhilFactor\n    Examples:\n       - Select * from dbo.JSONPathsAndValues(N'{\"person\":{\"info\":{\"name\":\"John\", \"name\":\"Jack\"}}}')\n       - Select * from MyTableWithJson cross apply dbo.JSONPathsAndValues(MyJSONColumn)\n    Returns: >\n    A table listing the paths to all the values in the JSON document \n    with their type and their order and nesting depth in the document\n   **\/\n    (@JSONData NVARCHAR(MAX))\n  RETURNS @TheHierarchyMetadata TABLE\n    (\n    -- columns returned by the function\n    element_id INT NOT NULL,\n    Depth INT NOT NULL,\n    Thepath NVARCHAR(2000),\n    ValueType VARCHAR(10) NOT NULL,\n    TheValue NVARCHAR(MAX) NOT NULL\n    )\n  AS\n    -- body of the function\n    BEGIN\n      DECLARE @ii INT = 1, @rowcount INT = -1;\n      DECLARE @null INT = 0, @string INT = 1, @int INT = 2, --\n        @boolean INT = 3, @array INT = 4, @object INT = 5;\n      DECLARE @TheHierarchy TABLE\n        (\n        element_id INT IDENTITY(1, 1) PRIMARY KEY,\n        Depth INT NOT NULL, \/* effectively, the recursion level. =the depth of nesting*\/\n        Thepath NVARCHAR(2000) NOT NULL,\n        TheName NVARCHAR(2000) NOT NULL,\n        TheValue NVARCHAR(MAX) NOT NULL,\n        ValueType VARCHAR(10) NOT NULL\n        );\n      INSERT INTO @TheHierarchy\n        (Depth, Thepath, TheName, TheValue, ValueType)\n        SELECT @ii, '<\/pre>\n
    With this, you can see what paths lead to the keys, and you can see what is in the values of those keys.<\/p>\n
    DECLARE @JSONData NVARCHAR(4000) = N'\n  {\n      \"info\": {\n          \"type\": 1,\n          \"address\": {\n              \"town\": \"Colchester\",\n              \"county\": \"Essex\",\n              \"country\": \"England\"\n          },\n          \"Hotels\": {\n              \"La Posche Hotel\": \"$400\",\n              \"The Salesmans Rest\": \"$35\",\n              \"The Middling Inn\": \"$100\"}\n      },\n      \"Sights\": [\"the Castle\",\"The Barracks\",\"the Hythe\",\"St Bartolphs\"]\n  } ';\n  SELECT * from dbo.JSONPathsAndValues(@JSONData) <\/pre>\n
    Which would give you this\u2026<\/p>\n
    <\/p>\n
    Differences between the metadata of two JSON strings<\/h1>\n
    It is very easy to check two JSON strings to make sure that they have the same metadata, and report any differences that it finds.<\/p>\n
    IF Object_Id('dbo.DifferenceBetweenJSONstrings') IS NOT NULL\n     DROP function dbo.DifferenceBetweenJSONstrings\n  GO\n  CREATE FUNCTION dbo.DifferenceBetweenJSONstrings\n  \/**\n  Summary: >\n    This checks two JSON strings and  returns\n    a table listing any differences in the schema between them\n  Author: PhilFactor\n  Date: 20\/10\/2017\n  Database: PhilFactor\n  Examples:\n     - Select * from dbo.DifferenceBetweenJSONstrings(@Original,@new)\n     - Select  from MyTable cross apply dbo.DifferenceBetweenJSONstrings(FirstJ)\n  Returns: >\n    A table\n          **\/\n    (\n    @Original nvarchar (max),-- the original JSON string\n    @New nvarchar (max)      -- the New JSON string\n    )\n  RETURNS TABLE\n   --WITH ENCRYPTION|SCHEMABINDING, ..\n  AS\n  RETURN\n    (\n    SELECT Coalesce(old.thePath, new.thepath) AS JSONpath,\n      Coalesce(old.valuetype, '')\n      + CASE WHEN old.valuetype + new.valuetype IS NOT NULL THEN ' \\ ' ELSE '' END\n      + Coalesce(new.valuetype, '') AS ValueType,\n      CASE WHEN old.valuetype + new.valuetype IS NOT NULL THEN 'value type changed'\n        WHEN old.thePath IS NULL THEN 'added or key changed'\n        WHEN new.thePath IS NULL THEN 'missing' ELSE 'dunno' END AS TheDifference\n      FROM dbo.JSONPathsAndValues(@New) AS new\n        FULL OUTER JOIN dbo.JSONPathsAndValues(@original) AS old\n          ON old.ThePath = new.ThePath --AND old.Valuetype=new.Valuetype\n      WHERE old.thepath IS NULL OR new.thepath IS NULL OR old.ValueType <> new.ValueType\n       \n    );\n  Go<\/pre>\n
    As part of the build, we\u2019d run some code like this to ensure that the function still does what you expect<\/p>\n
      Select * from dbo.DifferenceBetweenJSONstrings(\n    '[12,32,35,49,15,56,37]','[1,2,3,4,5,6]')\n      Select * from dbo.DifferenceBetweenJSONstrings(\n    '[1,2,3,4,5,6]','[1,\"2\",3,4,5,6]')\n       Select * from dbo.DifferenceBetweenJSONstrings(\n    '{\"id\": \"001\",\"type\": \"Coupe\",\"name\": \"Cougar\",\"year\": \"2012\"}',\n    '{\"id\": \"004\",\"type\": \"Coupe\",\"name\": \"Jaguar\",\"year\": \"2012\"}')\n        Select * from dbo.DifferenceBetweenJSONstrings(\n    '{\"id\": \"001\",\"type\": \"Coupe\",\"name\": \"Cougar\",\"year\": \"2012\"}',\n    '{\"id\": \"001\",\"type\": \"Coupe\",\"name\": \"Cougar\",\"start\": \"2012\"}')<\/pre>\n
    Actually, to be honest, I wouldn\u2019t do my tests this way in order to make sure the function works. I\u2019d run all the results into a table and make sure that the entire table was what I\u2019d expect. After all, we all do automated unit tests as part of our daily build don\u2019t we?<\/p>\n
      DECLARE @TestData TABLE (JSONpath NVARCHAR(MAX),ValueType VARCHAR(20), TheDifference VARCHAR(20))\n    INSERT INTO @testdata \n  \tSELECT JSONpath,ValueType,TheDifference \n  \t  FROM dbo.DifferenceBetweenJSONstrings(\n         '[12,32,35,49,15,56,37]','[1,2,3,4,5,6]')\n      UNION ALL SELECT JSONpath,ValueType,TheDifference \n  \t  FROM dbo.DifferenceBetweenJSONstrings(\n         '[1,2,3,4,5,6]','[1,\"2\",3,4,5,6]')\n      UNION all Select JSONpath,ValueType,TheDifference \n  \t  FROM dbo.DifferenceBetweenJSONstrings(\n    '{\"id\": \"001\",\"type\": \"Coupe\",\"name\": \"Cougar\",\"year\": \"2012\"}',\n    '{\"id\": \"004\",\"type\": \"Coupe\",\"name\": \"Jaguar\",\"year\": \"2012\"}')\n      UNION all  Select JSONpath,ValueType,TheDifference \n  \t  FROM dbo.DifferenceBetweenJSONstrings(\n    '{\"id\": \"001\",\"type\": \"Coupe\",\"name\": \"Cougar\",\"year\": \"2012\"}',\n    '{\"id\": \"001\",\"type\": \"Coupe\",\"name\": \"Cougar\",\"start\": \"2012\"}')\n  IF (EXISTS(  \n    SELECT * FROM @testData g\n      FULL OUTER JOIN \n       (VALUES('$[6]','int','missing'),\n         ('$[1]','int \\ string','value type changed'),\n         ('$.START','string','added or key changed'),\n         ('$.year','string','missing'))f(JSONpath,ValueType,TheDifference)\n      ON f.JSONpath=g.JSONpath\n    WHERE f.JSONpath IS NULL OR g.JSONpath IS NULL))\n  \tRAISERROR ('the dbo.DifferenceBetweenJSONstrings routine is\n  \tgiving unexpected results;',16,1)<\/pre>\n
    OK. We can now compare the metadata of two JSON strings, but we can compare the values as well if we ever need to check that two JSON documents represent the same data.<\/p>\n
    Checking that the JSON is what you expect<\/h1>\n
    Many Web Services send information messages or warning messages in JSON format instead of the data you expect. It could contain a variety of messages such as service interruption, subscription terminations, or daily credit-limits reached. These need to be logged and you need to bypass the import routine. Probably the quickest way to check your JSON is to do a simple JSON_VALUE call on a key\/value pair that needs to be there, to see if you get NULL back. To get that path in the first place, you can use the JSONPathsAndValues<\/strong> function. However, it is possible that there are a number of key\/value pairs that need to be there. There are plenty of ways of doing this if you have a list or table of the paths you need. I\u2019ll use a VALUES table-source to illustrate the point.<\/p>\n
    DECLARE @json NVARCHAR(MAX) = N'[  \n    {  \n      \"Order\": {  \n        \"Number\":\"SO43659\",  \n        \"Date\":\"2011-05-31T00:00:00\"  \n      },  \n      \"AccountNumber\":\"AW29825\",  \n      \"Item\": {  \n        \"Price\":2024.9940,  \n        \"Quantity\":1  \n      }  \n    },  \n    {  \n      \"Order\": {  \n        \"Number\":\"SO43661\",  \n        \"Date\":\"2011-06-01T00:00:00\"  \n      },  \n      \"AccountNumber\":\"AW73565\",  \n      \"Item\": {  \n        \"Price\":2024.9940,  \n        \"Quantity\":3  \n      }  \n    }\n  ]'  \n  IF EXISTS(\n  SELECT thepath\n  FROM \n  (VALUES\n  ('$[0].AccountNumber'),\n  ('$[0].Order.Number'),\n  ('$[0].Order.Date'),\n  ('$[0].Item.Price'),\n  ('$[0].Item.Quantity'))WhatThereShouldBe(path)\n  LEFT OUTER JOIN  dbo.JSONPathsAndValues( @json )  \n  ON WhatThereShouldBe.path=ThePath\n  WHERE ThePath IS NULL)\n  RAISERROR ('an essential key is missing',16,1)<\/pre>\n
    Comparing values as well as metadata<\/h1>\n
    A very small tweak in the code for checking the keys will allow you to compare the values as well if you ever need to do that. Note that it checks the order of lists too.<\/p>\n
    SELECT FirstVersion.Thepath, SecondVersion.Thepath, FirstVersion.TheValue, SecondVersion.TheValue\n    FROM dbo.JSONPathsAndValues('[1,2,3,4,5,7,8]') AS FirstVersion\n      FULL OUTER JOIN dbo.JSONPathsAndValues('[1,2,3,4,5,6]') AS SecondVersion\n        ON FirstVersion.Thepath = SecondVersion.Thepath\n    WHERE FirstVersion.TheValue <> SecondVersion.TheValue\n       OR FirstVersion.Thepath IS NULL\n       OR SecondVersion.Thepath IS NULL;<\/pre>\n
    <\/p>\n
    As you can see, there are two differences between the JSON strings. There are different values for the same array element (line 1 of result) and there are a different number of array elements (line 2).<\/p>\n
    Checking for valid SQL Datatypes.<\/h1>\n
    That is OK as far as it goes, but what about checking that the data will actually go into SQL Server. Although there are good practices for the storage of dates in JSON, for example, there is no JSON standard. If we know our constraints, it is dead easy to check. Imagine we have some JSON which is of a particular datatype. We just define the paths of the values that we want to check and perform whatever check we need on the JSON. We can demonstrate this technique.<\/p>\n
    SELECT g.ThePath, g.TheValue, CASE Coalesce(f.Datatype,'') \n  \t\tWHEN '' THEN  '' --it hasnt had a check specified \n  \t\tWHEN 'int' THEN --need to check that it is a valid int\n  \t\t CASE WHEN Try_Convert(INT,g.TheValue) IS NULL THEN 'Bad int' ELSE 'good' end\n  \t\tWHEN 'DateTime' THEN --need to check that it is a valid DateTime\n  \t\t CASE WHEN Try_Convert(DateTime,g.TheValue) IS NULL THEN 'Bad datetime' ELSE 'good' end\n  \t\tWHEN 'Money' THEN --need to check that it is a valid Money value\n  \t\t CASE WHEN Try_Convert(Money,g.TheValue) IS NULL THEN 'Bad money value' ELSE 'good' END\n          WHEN 'ISO8601' THEN --need to check that it is a valid ISO8601 datetime\n  \t\t CASE WHEN Try_Convert(DateTime,g.TheValue) IS NULL THEN 'Bad ISO8601 datetime' ELSE 'good' end\t\n  \t   ELSE '' end\n  \t\n  FROM dbo.JSONPathsAndValues('[\"1.7\",\"2\",\"23\/4\/2008\",\"1 Jun 2017\",\"5.6d\",\"$456,000\",\"2017-09-12T18:26:20.000\",\"2017\/10\/20T18:26:20.000\"]')g\n  LEFT outer JOIN\n       (VALUES('$[0]','int'),\n  \t   ('$[1]','int'),\n         ('$[2]','DateTime'),\n  \t   ('$[3]','DateTime'),\n  \t   ('$[4]','money'),\n  \t   ('$[5]','money'),\n  \t   ('$[6]','ISO8601'),\n  \t   ('$[7]','ISO8601'))f(Thepath,DataType)\n      ON f.thepath=g.Thepath<\/pre>\n
    \u00a0<\/p>\n
    Which, in this test case would give\u2026<\/p>\n
    <\/p>\n
    You will have appreciated that, as well as the JSON, you will need a separate table source to say what type of data each value should be, and I\u2019ve done a custom date format to show that you can refine your constraint. I\u2019ve used a VALUES table source, but you can easily swap in a JSON one. This table source needs to be kept in sync with the case statement for it to work. In a working system, you\u2019d want to encapsulate all this in a function.<\/p>\n
    Checking whether the values will pass constraint checks<\/h1>\n
    So how would you tackle the task of checking this simple example to make sure that all these IP addresses were valid? For this example we\u2019ll just check that there are just three dots. We don\u2019t want to check the name, obviously.<\/p>\n
    {  \n   \"name\":[  \n      \"Philip\",\n      \"Mildew\",\n      \"Factor\"\n   ],\n   \"ipAddress\":[  \n      \"80.243.543.4\",\n      \"45.85.678.68\",\n      \"5.8.7.9\",\n      \"192.168.0.123\",\n      \"34.8.8\"\n   ]\n}\n<\/pre>\n
    You wouldn\u2019t want that last IP address in your database. It isn\u2019t valid. You only want to check those IP values. Here, you can very easily run the check.<\/p>\n
    SELECT ThePath, TheValue \n   FROM dbo.JSONPathsAndValues('{\"name\":[\"Philip\",\"Mildew\",\"Factor\"],\"ipAddress\":[ \"80.243.543.4\",\"45.85.678.68\",\"5.8.7.9\",\"192.168.0.123\",\"34.8.8\"]}')\n  WHERE ThePath LIKE '$.ipAddress%' AND TheValue NOT LIKE  '%.%.%.%'<\/pre>\n
    And you will see the bad IP address, but it only runs the check on the list of IP addresses, which is what you want.<\/p>\n
    <\/p>\n
    If you had a number of checks to do, you\u2019d save the table-source as a table variable or temporary table and run several queries on it.<\/p>\n
    Scaling things up<\/h1>\n
    Normally you are dealing with large amounts of JSON data, so you only want to parse it once into a temporary table or table variable before running all your metadata checks. The advantage of doing it this way is that, once you\u2019ve put a good primary key on the path (beware of duplicate JSON keys: they are valid JSON – RFC 4627), the process of firstly checking that the data can be successfully coerced into the appropriate column of the destination table, and then checked that it is within bounds before finally unpicking the hierarchical JSON data into all the relational tables in the right order is much easier and well-controlled.<\/p>\n
    Using a JSON Webservice from SQL Server<\/h1>\n
    Normally, you\u2019d use SSIS or Powershell for a regular production data feed, but it is certainly possible to do it in SQL. The downside is that your SQL Server needs to have internet access, which is a security risk, and also you have to open up your security surface-area by allowing OLE automation. That said, this is how you do it.<\/p>\n
    IF NOT EXISTS (SELECT * FROM sys.configurations WHERE name ='Ole Automation Procedures' AND value=1)\n  \tBEGIN\n     EXECUTE sp_configure 'Ole Automation Procedures', 1;\n     RECONFIGURE;  \n     end \n  SET ANSI_NULLS ON;\n  SET QUOTED_IDENTIFIER ON;\n  GO\n  IF Object_Id('dbo.GetWebService','P') IS NOT NULL \n  \tDROP procedure dbo.GetWebService\n  GO\n  CREATE PROCEDURE dbo.GetWebService\n    @TheURL VARCHAR(255),-- the url of the web service\n    @TheResponse NVARCHAR(4000) OUTPUT --the resulting JSON\n  AS\n    BEGIN\n      DECLARE @obj INT, @hr INT, @status INT, @message VARCHAR(255);\n      \/**\n  Summary: >\n    This is intended for using web services that \n    utilize JavaScript Object Notation (JSON). You pass it the link to\n    a webservice and it returns the JSON string\n  Note: >\n    OLE Automation objects can be used within a Transact-SQL batch, but \n    SQL Server blocks access to OLE Automation stored procedures because\n    this component is turned off as part of the security configuration.\n   \n  Author: PhilFactor\n  Date: 26\/10\/2017\n  Database: PhilFactor\n  Examples:\n     - >\n     DECLARE @response NVARCHAR(MAX) \n     EXECUTE dbo.GetWebService 'http:\/\/headers.jsontest.com\/', @response OUTPUT\n     SELECT  @response \n  Returns: >\n    nothing\n  **\/\n      EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT;\n      SET @message = 'sp_OAMethod Open failed';\n      IF @hr = 0 EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'GET', @TheURL, false;\n      SET @message = 'sp_OAMethod setRequestHeader failed';\n      IF @hr = 0\n        EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',\n          'application\/x-www-form-urlencoded';\n      SET @message = 'sp_OAMethod Send failed';\n      IF @hr = 0 EXEC @hr = sp_OAMethod @obj, send, NULL, '';\n      SET @message = 'sp_OAMethod read status failed';\n      IF @hr = 0 EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT;\n      IF @status <> 200 BEGIN\n                          SELECT @message = 'sp_OAMethod http status ' + Str(@status), @hr = -1;\n        END;\n      SET @message = 'sp_OAMethod read response failed';\n      IF @hr = 0\n        BEGIN\n          EXEC @hr = sp_OAGetProperty @obj, 'responseText', @Theresponse OUT;\n          END;\n      EXEC sp_OADestroy @obj;\n      IF @hr <> 0 RAISERROR(@message, 16, 1);\n      END;\n  GO<\/pre>\n
    To use this is simple. You can use this on any of the JSONTest samples<\/a><\/p>\n
    DECLARE @response NVARCHAR(MAX) \n  EXECUTE dbo.GetWebService 'http:\/\/headers.jsontest.com\/', @response OUTPUT\n  SELECT  @response<\/pre>\n
    Which will give you \u2026<\/p>\n
    {\n     \"X-Cloud-Trace-Context\": \"54e570f5620dc6ef3b087ac6042dca03\/10421626717945848480\",\n     \"Host\": \"headers.jsontest.com\",\n     \"User-Agent\": \"Mozilla\/4.0 (compatible; Win32; WinHttp.WinHttpRequest.5)\",\n     \"Accept\": \"*\/*\",\n     \"Content-Type\": \"application\/x-www-form-urlencoded\"\n  }<\/pre>\n
    Now that we have the means to get a real service we can try it out. To try out the next example, you need to register with GeoNames.org<\/a>, but they are good people running a free and useful service. (If you decide to use it in production, make sure you buy support).<\/p>\n
    Imagine that you need to find the exact geographical coordinated for any postal code in the world. You can now do this, or a whole range of geographical services. For this example, though, we\u2019ll just list the capitals of all the countries in a defined area, together with their populations and longitude\/latitude coordinates.<\/p>\n
    This web service is liable to send you messages instead of your data so be sure to check and log these.<\/p>\n
    status\": {\n    \"message\": \"the hourly limit of 2000 credits for demo has been exceeded. Please use an application specific account. Do not use the demo account for your application.\",\n    \"value\": 19\n  }}<\/pre>\n
    In the following batch, we are merely showing this sort of message as an error.<\/p>\n
    Msg 50000, Level 16, State 1, Line 30\n  The import failed ({\"status\": {\n    \"message\": \"the hourly limit of 2000 credits for demo has been exceeded. Please use an application specific account. Do not use the demo account for your application.\",\n    \"value\": 19\n  }})<\/pre>\n
    In this case, I should have used my own account rather than demo mode. Although the \u2018demo\u2019 name in the URL will work a few times per hour, you will need to change this for your own registered name if you\u2019re getting stuck in.<\/p>\n
    DECLARE @response NVARCHAR(4000);\n  --get the data from the provider as JSON\n  EXECUTE dbo.GetWebService 'http:\/\/api.geonames.org\/citiesJSON?formatted=false&north=44.1&south=-9.9&east=-22.4&west=55.2&username=demo&style=full',\n    @response OUTPUT;\n  --now check to see if it is all there.\n  IF EXISTS\n    (\n    SELECT * FROM dbo.JSONPathsAndValues(@response)\n      WHERE Thepath IN\n  ('$.geonames[0].lng', '$.geonames[0].geonameId', '$.geonames[0].countrycode', '$.geonames[0].name',\n    '$.geonames[0].fclName', '$.geonames[0].toponymName', '$.geonames[0].fcodeName',\n    '$.geonames[0].wikipedia', '$.geonames[0].lat', '$.geonames[0].fcl', '$.geonames[0].population',\n    '$.geonames[0].fcode'\n  )\n    )\n    BEGIN\n      SELECT CountryCode, name, population, latitude, longitude, id, WikipediaURL\n        FROM OpenJson(@response) --we have to walk to the level of the array that\n  \t  --we are interested in. OPENjson doesn't support accessing an array at \n  \t  --a higher level when using the WITH clause.\n          OUTER APPLY\n        OpenJson(Value)\n        WITH\n          (CountryCode CHAR(2) '$.countrycode', Latitude NUMERIC(38, 15) '$.lat',\n          Longitude NUMERIC(38, 15) '$.lng', Name VARCHAR(200) '$.name',\n          Population BIGINT '$.population', wikipediaURL VARCHAR(200) '$.wikipedia',\n          id INT '$.geonameId'\n          );\n    END;\n  ELSE RAISERROR('The import failed (%s)', 16, 1, @response);<\/pre>\n
    If all is well, this will give you a result something like this\u2026<\/p>\n
    <\/p>\n
    Of course, there is a lot more you can do. You can, for example, check that the data fits the datatypes in the table, and do whatever other constraint checks you need.<\/p>\n
    You can of xcourse dispense with the gymnastics of that last bit of openJSON by using what is in the output of dbo.JSONPathsAndValues<\/strong><\/p>\n
    DECLARE @response NVARCHAR(4000);\n  --get the data from the provider as JSON\n  EXECUTE dbo.GetWebService 'http:\/\/api.geonames.org\/citiesJSON?formatted=false&north=44.1&south=-9.9&east=-22.4&west=55.2&username=demo&style=full',\n    @response OUTPUT;\n  DECLARE @TheData  table (\n    -- columns returned by the function\n    element_id INT NOT NULL,\n    Depth INT NOT NULL,\n    Thepath NVARCHAR(2000),\n    ValueType VARCHAR(10) NOT NULL,\n    TheValue NVARCHAR(MAX) NOT NULL\n    )\n   INSERT INTO @TheData SELECT * FROM dbo.JSONPathsAndValues(@response)\n   SELECT \n   Max(Convert(CHAR(2),CASE WHEN Thepath LIKE '%.countrycode' THEN Thevalue ELSE '' END)) AS countycode,\n   Max(Convert(NVARCHAR(200),CASE WHEN Thepath LIKE '%.name' THEN Thevalue ELSE '' END)) AS name,\n   Max(Convert(NUMERIC(38, 15),CASE WHEN Thepath LIKE '%.lat' THEN Thevalue ELSE '-90' END)) AS latitude,\n   Max(Convert(NUMERIC(38, 15),CASE WHEN Thepath LIKE '%.lng' THEN Thevalue ELSE '-180' END)) AS longitude,\n   Max(Convert(BigInt,CASE WHEN Thepath LIKE '%.population' THEN Thevalue ELSE '-1' END)) AS population,\n   Max(Convert(VARCHAR(200),CASE WHEN Thepath LIKE '%.wikipedia' THEN Thevalue ELSE '' END)) AS wikipediaURL,\n   Max(Convert (INT,CASE WHEN Thepath LIKE '%.geonameId' THEN Thevalue ELSE '0' END)) AS ID\n   FROM @TheData GROUP BY Left(ThePath,CharIndex(']',ThePath+']'))<\/pre>\n
    This gives the same result but saving some parsing.<\/p>\n
    <\/p>\n
    Summary<\/h1>\n
    I\u2019ve set out to show how you can run all manner of checks before importing some JSON into SQL Server, such as ensuring that the JSON metadata is what you expect, finding out the full paths of the information you want, or checking that the values are valid for your datatypes and within range. It is easy to work out what is in a JSON document without having to inspect it. By showing you a simple feed from a web service, I hope I\u2019ve shown how you can make it as robust as you require. There is a lot more you can do, of course, but I hope I\u2019ve shown you enough to enable you to feel confident about accepting data in JSON format.<\/p>\n
    , '<\/pre>\n
    With this, you can see what paths lead to the keys, and you can see what is in the values of those keys.<\/p>\n
    <\/pre>\n
    Which would give you this\u2026<\/p>\n
    <\/p>\n
    Differences between the metadata of two JSON strings<\/h1>\n
    It is very easy to check two JSON strings to make sure that they have the same metadata, and report any differences that it finds.<\/p>\n
    <\/pre>\n
    As part of the build, we\u2019d run some code like this to ensure that the function still does what you expect<\/p>\n
    <\/pre>\n
    Actually, to be honest, I wouldn\u2019t do my tests this way in order to make sure the function works. I\u2019d run all the results into a table and make sure that the entire table was what I\u2019d expect. After all, we all do automated unit tests as part of our daily build don\u2019t we?<\/p>\n
    <\/pre>\n
    OK. We can now compare the metadata of two JSON strings, but we can compare the values as well if we ever need to check that two JSON documents represent the same data.<\/p>\n
    Checking that the JSON is what you expect<\/h1>\n
    Many Web Services send information messages or warning messages in JSON format instead of the data you expect. It could contain a variety of messages such as service interruption, subscription terminations, or daily credit-limits reached. These need to be logged and you need to bypass the import routine. Probably the quickest way to check your JSON is to do a simple JSON_VALUE call on a key\/value pair that needs to be there, to see if you get NULL back. To get that path in the first place, you can use the JSONPathsAndValues<\/strong> function. However, it is possible that there are a number of key\/value pairs that need to be there. There are plenty of ways of doing this if you have a list or table of the paths you need. I\u2019ll use a VALUES table-source to illustrate the point.<\/p>\n
    <\/pre>\n
    Comparing values as well as metadata<\/h1>\n
    A very small tweak in the code for checking the keys will allow you to compare the values as well if you ever need to do that. Note that it checks the order of lists too.<\/p>\n
    <\/pre>\n
    <\/p>\n
    As you can see, there are two differences between the JSON strings. There are different values for the same array element (line 1 of result) and there are a different number of array elements (line 2).<\/p>\n
    Checking for valid SQL Datatypes.<\/h1>\n
    That is OK as far as it goes, but what about checking that the data will actually go into SQL Server. Although there are good practices for the storage of dates in JSON, for example, there is no JSON standard. If we know our constraints, it is dead easy to check. Imagine we have some JSON which is of a particular datatype. We just define the paths of the values that we want to check and perform whatever check we need on the JSON. We can demonstrate this technique.<\/p>\n
    <\/pre>\n
    \u00a0<\/p>\n
    Which, in this test case would give\u2026<\/p>\n
    <\/p>\n
    You will have appreciated that, as well as the JSON, you will need a separate table source to say what type of data each value should be, and I\u2019ve done a custom date format to show that you can refine your constraint. I\u2019ve used a VALUES table source, but you can easily swap in a JSON one. This table source needs to be kept in sync with the case statement for it to work. In a working system, you\u2019d want to encapsulate all this in a function.<\/p>\n
    Checking whether the values will pass constraint checks<\/h1>\n
    So how would you tackle the task of checking this simple example to make sure that all these IP addresses were valid? For this example we\u2019ll just check that there are just three dots. We don\u2019t want to check the name, obviously.<\/p>\n
    <\/pre>\n
    You wouldn\u2019t want that last IP address in your database. It isn\u2019t valid. You only want to check those IP values. Here, you can very easily run the check.<\/p>\n
    <\/pre>\n
    And you will see the bad IP address, but it only runs the check on the list of IP addresses, which is what you want.<\/p>\n
    <\/p>\n
    If you had a number of checks to do, you\u2019d save the table-source as a table variable or temporary table and run several queries on it.<\/p>\n
    Scaling things up<\/h1>\n
    Normally you are dealing with large amounts of JSON data, so you only want to parse it once into a temporary table or table variable before running all your metadata checks. The advantage of doing it this way is that, once you\u2019ve put a good primary key on the path (beware of duplicate JSON keys: they are valid JSON – RFC 4627), the process of firstly checking that the data can be successfully coerced into the appropriate column of the destination table, and then checked that it is within bounds before finally unpicking the hierarchical JSON data into all the relational tables in the right order is much easier and well-controlled.<\/p>\n
    Using a JSON Webservice from SQL Server<\/h1>\n
    Normally, you\u2019d use SSIS or Powershell for a regular production data feed, but it is certainly possible to do it in SQL. The downside is that your SQL Server needs to have internet access, which is a security risk, and also you have to open up your security surface-area by allowing OLE automation. That said, this is how you do it.<\/p>\n
    <\/pre>\n
    To use this is simple. You can use this on any of the JSONTest samples<\/a><\/p>\n
    <\/pre>\n
    Which will give you \u2026<\/p>\n
    <\/pre>\n
    Now that we have the means to get a real service we can try it out. To try out the next example, you need to register with GeoNames.org<\/a>, but they are good people running a free and useful service. (If you decide to use it in production, make sure you buy support).<\/p>\n
    Imagine that you need to find the exact geographical coordinated for any postal code in the world. You can now do this, or a whole range of geographical services. For this example, though, we\u2019ll just list the capitals of all the countries in a defined area, together with their populations and longitude\/latitude coordinates.<\/p>\n
    This web service is liable to send you messages instead of your data so be sure to check and log these.<\/p>\n
    <\/pre>\n
    In the following batch, we are merely showing this sort of message as an error.<\/p>\n
    <\/pre>\n
    In this case, I should have used my own account rather than demo mode. Although the \u2018demo\u2019 name in the URL will work a few times per hour, you will need to change this for your own registered name if you\u2019re getting stuck in.<\/p>\n
    <\/pre>\n
    If all is well, this will give you a result something like this\u2026<\/p>\n
    <\/p>\n
    Of course, there is a lot more you can do. You can, for example, check that the data fits the datatypes in the table, and do whatever other constraint checks you need.<\/p>\n
    You can of xcourse dispense with the gymnastics of that last bit of openJSON by using what is in the output of dbo.JSONPathsAndValues<\/strong><\/p>\n
    <\/pre>\n
    This gives the same result but saving some parsing.<\/p>\n
    <\/p>\n
    Summary<\/h1>\n
    I\u2019ve set out to show how you can run all manner of checks before importing some JSON into SQL Server, such as ensuring that the JSON metadata is what you expect, finding out the full paths of the information you want, or checking that the values are valid for your datatypes and within range. It is easy to work out what is in a JSON document without having to inspect it. By showing you a simple feed from a web service, I hope I\u2019ve shown how you can make it as robust as you require. There is a lot more you can do, of course, but I hope I\u2019ve shown you enough to enable you to feel confident about accepting data in JSON format.<\/p>\n
    , @JSONData, 'object'; WHILE @rowcount <> 0 BEGIN SELECT @ii = @ii + 1; INSERT INTO @TheHierarchy (Depth, Thepath, TheName, TheValue, ValueType) SELECT @ii, CASE WHEN [Key] NOT LIKE '%[^0-9]%' THEN Thepath + '[' + [Key] + ']' --nothing but numbers WHEN [Key] LIKE '%[$ ]%' THEN Thepath + '.\"' + [Key] + '\"' --got a space in it ELSE Thepath + '.' + [Key] END, [Key], Coalesce(Value,''), CASE Type WHEN @string THEN 'string' WHEN @null THEN 'null' WHEN @int THEN 'int' WHEN @boolean THEN 'boolean' WHEN @int THEN 'int' WHEN @array THEN 'array' ELSE 'object' END FROM @TheHierarchy AS m CROSS APPLY OpenJson(TheValue) AS o WHERE ValueType IN ('array', 'object') AND Depth = @ii - 1; SELECT @rowcount = @@RowCount; END; INSERT INTO @TheHierarchyMetadata SELECT element_id, Depth, Thepath, ValueType, TheValue FROM @TheHierarchy WHERE ValueType NOT IN ('array', 'object'); RETURN; END; GO<\/pre>\n
    With this, you can see what paths lead to the keys, and you can see what is in the values of those keys.<\/p>\n
    <\/pre>\n
    Which would give you this\u2026<\/p>\n
    <\/p>\n
    Differences between the metadata of two JSON strings<\/h1>\n
    It is very easy to check two JSON strings to make sure that they have the same metadata, and report any differences that it finds.<\/p>\n
    <\/pre>\n
    As part of the build, we\u2019d run some code like this to ensure that the function still does what you expect<\/p>\n
    <\/pre>\n
    Actually, to be honest, I wouldn\u2019t do my tests this way in order to make sure the function works. I\u2019d run all the results into a table and make sure that the entire table was what I\u2019d expect. After all, we all do automated unit tests as part of our daily build don\u2019t we?<\/p>\n
    <\/pre>\n
    OK. We can now compare the metadata of two JSON strings, but we can compare the values as well if we ever need to check that two JSON documents represent the same data.<\/p>\n
    Checking that the JSON is what you expect<\/h1>\n
    Many Web Services send information messages or warning messages in JSON format instead of the data you expect. It could contain a variety of messages such as service interruption, subscription terminations, or daily credit-limits reached. These need to be logged and you need to bypass the import routine. Probably the quickest way to check your JSON is to do a simple JSON_VALUE call on a key\/value pair that needs to be there, to see if you get NULL back. To get that path in the first place, you can use the JSONPathsAndValues<\/strong> function. However, it is possible that there are a number of key\/value pairs that need to be there. There are plenty of ways of doing this if you have a list or table of the paths you need. I\u2019ll use a VALUES table-source to illustrate the point.<\/p>\n
    <\/pre>\n
    Comparing values as well as metadata<\/h1>\n
    A very small tweak in the code for checking the keys will allow you to compare the values as well if you ever need to do that. Note that it checks the order of lists too.<\/p>\n
    <\/pre>\n
    <\/p>\n
    As you can see, there are two differences between the JSON strings. There are different values for the same array element (line 1 of result) and there are a different number of array elements (line 2).<\/p>\n
    Checking for valid SQL Datatypes.<\/h1>\n
    That is OK as far as it goes, but what about checking that the data will actually go into SQL Server. Although there are good practices for the storage of dates in JSON, for example, there is no JSON standard. If we know our constraints, it is dead easy to check. Imagine we have some JSON which is of a particular datatype. We just define the paths of the values that we want to check and perform whatever check we need on the JSON. We can demonstrate this technique.<\/p>\n
    <\/pre>\n
    \u00a0<\/p>\n
    Which, in this test case would give\u2026<\/p>\n
    <\/p>\n
    You will have appreciated that, as well as the JSON, you will need a separate table source to say what type of data each value should be, and I\u2019ve done a custom date format to show that you can refine your constraint. I\u2019ve used a VALUES table source, but you can easily swap in a JSON one. This table source needs to be kept in sync with the case statement for it to work. In a working system, you\u2019d want to encapsulate all this in a function.<\/p>\n
    Checking whether the values will pass constraint checks<\/h1>\n
    So how would you tackle the task of checking this simple example to make sure that all these IP addresses were valid? For this example we\u2019ll just check that there are just three dots. We don\u2019t want to check the name, obviously.<\/p>\n
    <\/pre>\n
    You wouldn\u2019t want that last IP address in your database. It isn\u2019t valid. You only want to check those IP values. Here, you can very easily run the check.<\/p>\n
    <\/pre>\n
    And you will see the bad IP address, but it only runs the check on the list of IP addresses, which is what you want.<\/p>\n
    <\/p>\n
    If you had a number of checks to do, you\u2019d save the table-source as a table variable or temporary table and run several queries on it.<\/p>\n
    Scaling things up<\/h1>\n
    Normally you are dealing with large amounts of JSON data, so you only want to parse it once into a temporary table or table variable before running all your metadata checks. The advantage of doing it this way is that, once you\u2019ve put a good primary key on the path (beware of duplicate JSON keys: they are valid JSON – RFC 4627), the process of firstly checking that the data can be successfully coerced into the appropriate column of the destination table, and then checked that it is within bounds before finally unpicking the hierarchical JSON data into all the relational tables in the right order is much easier and well-controlled.<\/p>\n
    Using a JSON Webservice from SQL Server<\/h1>\n
    Normally, you\u2019d use SSIS or Powershell for a regular production data feed, but it is certainly possible to do it in SQL. The downside is that your SQL Server needs to have internet access, which is a security risk, and also you have to open up your security surface-area by allowing OLE automation. That said, this is how you do it.<\/p>\n
    <\/pre>\n
    To use this is simple. You can use this on any of the JSONTest samples<\/a><\/p>\n
    <\/pre>\n
    Which will give you \u2026<\/p>\n
    <\/pre>\n
    Now that we have the means to get a real service we can try it out. To try out the next example, you need to register with GeoNames.org<\/a>, but they are good people running a free and useful service. (If you decide to use it in production, make sure you buy support).<\/p>\n
    Imagine that you need to find the exact geographical coordinated for any postal code in the world. You can now do this, or a whole range of geographical services. For this example, though, we\u2019ll just list the capitals of all the countries in a defined area, together with their populations and longitude\/latitude coordinates.<\/p>\n
    This web service is liable to send you messages instead of your data so be sure to check and log these.<\/p>\n
    <\/pre>\n
    In the following batch, we are merely showing this sort of message as an error.<\/p>\n
    <\/pre>\n
    In this case, I should have used my own account rather than demo mode. Although the \u2018demo\u2019 name in the URL will work a few times per hour, you will need to change this for your own registered name if you\u2019re getting stuck in.<\/p>\n
    <\/pre>\n
    If all is well, this will give you a result something like this\u2026<\/p>\n
    <\/p>\n
    Of course, there is a lot more you can do. You can, for example, check that the data fits the datatypes in the table, and do whatever other constraint checks you need.<\/p>\n
    You can of xcourse dispense with the gymnastics of that last bit of openJSON by using what is in the output of dbo.JSONPathsAndValues<\/strong><\/p>\n
    <\/pre>\n
    This gives the same result but saving some parsing.<\/p>\n
    <\/p>\n
    Summary<\/h1>\n
    I\u2019ve set out to show how you can run all manner of checks before importing some JSON into SQL Server, such as ensuring that the JSON metadata is what you expect, finding out the full paths of the information you want, or checking that the values are valid for your datatypes and within range. It is easy to work out what is in a JSON document without having to inspect it. By showing you a simple feed from a web service, I hope I\u2019ve shown how you can make it as robust as you require. There is a lot more you can do, of course, but I hope I\u2019ve shown you enough to enable you to feel confident about accepting data in JSON format.<\/p>\n\n\n
    \n    
    FAQs: Importing JSON Data from Web Services and Applications into SQL Server<\/h2>\n\n                        
    1. How do I validate JSON before inserting it into SQL Server?<\/h3>\n            
    \n                
    Use ISJSON() to confirm the string is valid JSON, then use OPENJSON() with a WITH clause to check that required properties exist with the expected data types. Use JSON_VALUE() to extract and cast individual values to SQL Server types, catching conversion errors as validation failures. For constraint validation (checking that values meet business rules like valid IP ranges or foreign key existence), add WHERE-clause checks against extracted values before the final INSERT.<\/p>\n            <\/div>\n                    
    2. How do I parse JSON path expressions in SQL Server?<\/h3>\n            
    \n                
    SQL Server uses lax path mode by default: JSON_VALUE(@json, ‘$.property’) returns NULL if the path doesn’t exist rather than throwing an error. Use strict mode – JSON_VALUE(@json, ‘strict $.property’) – to raise an error when a required path is missing. OPENJSON() returns a table with key, value, and type columns for each JSON element, letting you inspect path structure programmatically without knowing the JSON schema in advance.<\/p>\n            <\/div>\n                    
    3. Can I call a JSON web service from SQL Server T-SQL?<\/h3>\n            
    \n                
    Yes, using sp_invoke_external_rest_endpoint in SQL Server 2022 Azure SQL Database (Azure SQL Database and SQL Managed Instance), or using OPENROWSET with OLE DB providers in earlier versions. For production data pipelines, SSIS or PowerShell is more robust and observable. Direct SQL web service calls are useful for ad hoc queries or simple scheduled imports where full ETL infrastructure is not warranted.<\/p>\n            <\/div>\n                    
    4. How do I compare the metadata of two JSON strings in SQL Server?<\/h3>\n            
    \n                
    Use OPENJSON() on both strings and compare the key-type structures returned. Extract the path expressions from each using a recursive OPENJSON call that traverses the hierarchy, then use EXCEPT or FULL OUTER JOIN between the two path sets to identify differences. The routine in this article returns paths present in one document but missing from the other, flagging structural differences before any data comparison is attempted.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
    Import and validate JSON from web services into SQL Server using T-SQL. Covers path expression parsing, metadata comparison, data type validation, constraint checking, and calling web services directly from SQL. Phil Factor’s JSON series.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143531],"tags":[5134],"coauthors":[6813],"class_list":["post-75102","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/75102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=75102"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/75102\/revisions"}],"predecessor-version":[{"id":109609,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/75102\/revisions\/109609"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=75102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=75102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=75102"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=75102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}