Also in the With clause, I define the data types for those columns which, among other benefits, allows me to convert the string-value date in the object into a genuine SQL Server date. The OpenJson's With clause allows me to select property names within my JSON document and return them as if they were columns in a table just by giving my columns names that match properties in my JSON object. In my examples, I'll use Insert Into which supports both scenarios.
In a production system, rather than use a Table variable, I could truncate an existing table on disk and load the rows from my JSON object into it alternatively, I could skip truncating the existing table and just append the rows from my JSON object. My first step, therefore, is to define a Table variable to hold those properties:ĭeclare Table (id nvarchar(100), createdOn Date) Select = value from I need to deal with the simple properties on the JSON document by loading them into a Table variable.
Visualize json code#
OpenJson prefers JSON to be held in NVarChar(Max) variables so that's how I declare my string variable: Declare nvarchar(MAX) īy default, OpenJson will refer to the value of the array inside the customers property as "value" so this code declares a string variable called CustomerHold and then loads it with that property from my JSON object: My first step is to strip the JSON collection of customer objects out of the customers property it's nested inside of and put it in a string variable to use in subsequent steps. In an upcoming post, I'll look at setting up both salesToDate and addresses as separate tables from that Customers table. In this post, I'm going to look at flattening salesToDate into a row with simple properties in a Customers table. The collection of addresses probably gives you less leeway - those addresses probably do belong in a related Address table to support the variety of addresses a customer might have (not only "shipping" and "billing" but, potentially, "contact" and "alternate shipping"). On the other hand, you might prefer to move salesToDate into a separate table.
That may make sense if you're targeting a data warehouse where you may not want to take your data to third or fourth normal form - simply eliminating repeated fields might be sufficient for reporting purposes. In this scenario, the properties in salesToDate could, for example, be converted into columns called salesToDateAmount and salesToDateCurrency. One solution to moving this structure to relational tables is just to flatten the JSON into a single table with columns like id and createdOn. Specifically, this JSON sample with both simple and complex properties (and a complex property with repeating values): I looked at OpenJson in an earlier post, but I'm going to return to the topic with a more practical focus: to address a specific real-world case with a typical JSON object as my example. If, however, you want to get your data into some sort of SQL database (either to support your transactional systems or as the first step to get your data into a data warehouse) and you're not interested in a full-fledged Extract, Transform and Load solution, SQL Server's OpenJson function might be all you need.
0 kommentar(er)
