CODEWITHSUNDEEP
jsonsql-serverssis
UserError_
UserError_

Reputation: 11

JSON to SQL Server

I have access to JSON data, saved in a SQL server database. I'd like to create a data model for reporting.

Here's what my data looks like now:

+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
|  Order_Id  |                                                                       JSON_Detail                                                                        |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Order_1001 | {"Customer":"Mario","BillingNumber":"99","List_ABC_0":"475","List_ABC_1":"461","List_ABC_2":"940","List_Type_0":"N","List_Type_1":"D","List_Type_2":"N"} |
| Order_1002 | {"Customer":"Luigi","BillingNumber":"61","List_ABC_0":"182","List_Type_0":"N"}                                                                           |
| Order_1003 | {"Customer":"Toad","BillingNumber":"03","List_ABC_0":"028","List_ABC_1":"283","List_Type_0":"D","List_Type_1":"D"}                                       |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

Using SSIS I can break this out into a flat table with fields for the JSON values. I get this as a result:

+------------+----------+---------------+------------+------------+------------+-------------+-------------+-------------+
|  Order_Id  | Customer | BillingNumber | List_ABC_0 | List_ABC_1 | List_ABC_2 | List_Type_0 | List_Type_1 | List_Type_2 |
+------------+----------+---------------+------------+------------+------------+-------------+-------------+-------------+
| Order_1001 | Mario    |            99 |        475 |        461 |        940 | N           | D           | N           |
| Order_1002 | Luigi    |            61 |        182 |            |            | N           |             |             |
| Order_1003 | Toad     |            03 |        028 |        283 |            | D           | D           |             |
+------------+----------+---------------+------------+------------+------------+-------------+-------------+-------------+

Now here's my issue, take note of the List_%_n fields (ABC and Type). In my sample I've capped it at 3, but my real data can have more.
I want to combine the each of the list types into a consolidated fields. My desired end result would be like this:

*I've added a sequence field, corresponding to n

+------------+-----------+----------+---------------+-----+------+
|  Order_Id  | Sequence  | Customer | BillingNumber | ABC | Type |
+------------+-----------+----------+---------------+-----+------+
| Order_1001 |         0 | Mario    |            99 | 475 | N    |
| Order_1001 |         1 | Mario    |            99 | 461 | D    |
| Order_1001 |         2 | Mario    |            99 | 940 | N    |
| Order_1002 |         0 | Luigi    |            61 | 182 | N    |
| Order_1003 |         0 | Toad     |            03 | 028 | D    |
| Order_1003 |         1 | Toad     |            03 | 283 | D    |
+------------+-----------+----------+---------------+-----+------+

How do I go from current state to the desired output?

SSIS and SQL Server are the tools I have access to.
I have write access (to create/alter tables, views, functions, stored proc, etc...) on the server.

Upvotes: 1

Views: 352

Answers (1)

DxTx
DxTx

Reputation: 3377

Try this...

SELECT order_id, customer, billingnumber, abc, [type], 
       Row_number() OVER (partition BY order_id ORDER BY order_id) - 1 AS [Sequence] 
FROM   tablename 
       CROSS apply (SELECT list_abc_0, list_type_0 UNION ALL 
                    SELECT list_abc_1, list_type_1 UNION ALL 
                    SELECT list_abc_2, list_type_2) Crs (abc, [Type]) 
WHERE  abc IS NOT NULL AND type IS NOT NULL;

To create a new table from the old table...

Select * into new_table  from (
SELECT order_id, customer, billingnumber, abc, [type], 
       Row_number() OVER (partition BY order_id ORDER BY order_id) - 1 AS [Sequence] 
FROM   tablename 
       CROSS apply (SELECT list_abc_0, list_type_0 UNION ALL 
                    SELECT list_abc_1, list_type_1 UNION ALL 
                    SELECT list_abc_2, list_type_2) Crs (abc, [Type]) 
WHERE  abc IS NOT NULL AND type IS NOT NULL) tmp

Output

+-------------+----------+---------------+-----+------+----------+
|  order_id   | customer | billingnumber | abc | type | Sequence |
+-------------+----------+---------------+-----+------+----------+
| Order_1001  | Mario    |            99 | 475 | N    |        0 |
| Order_1001  | Mario    |            99 | 461 | D    |        1 |
| Order_1001  | Mario    |            99 | 940 | N    |        2 |
| Order_1002  | Luigi    |            61 | 182 | N    |        0 |
| Order_1003  | Toad     |            03 | 028 | D    |        0 |
| Order_1003  | Toad     |            03 | 283 | D    |        1 |
+-------------+----------+---------------+-----+------+----------+

Online Demo: http://www.sqlfiddle.com/#!18/c0168/5/1

Upvotes: 1

Related Questions