Array into table.
-
Hi
I'm tasked with importing data into SQL thats pretty much JSON but not quite . I've used OPENROWSET/OPENJSON to import into a staging table and the data looks like this
What I need to achieve is migrate that to a single table with the following structure
I'm having no success , I even trying updating the data in the staging table to look like this and import but no joy.
Any recommendations ?
-
- Copy your data into a staging table.
- Replace all opening square brackets "[" and quote marks with space(0) to make your string look like this:
1613347200,7],1613347205,6],1613347210,7]
- Replace all close square brackets comma "]," with a vertical bar
1613347200,7|1613347205,6|1613347210,7]
- Replace the final "]" with a space (0) and you have
1613347200,7|1613347205,6|1613347210,7
- Get yourself a copy of the function DelimitedSplit8k()
https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2,
cross apply it to your staging table specifying "|" for a delimiter, and it will produce multiple rows like this:
1613347200,7
1613347205,6
1613347210,7
- Parse the CSV strings above using the LEFT() and RIGHT() functions based on the position of the comma given by CHARINDEX().Please don't be insulted by my including step 6. You obviously have enough skills to not need the explanation. It was included for completeness. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
You should be able to parse this using OPENJSON
declare @data table(instance varchar(20),array nvarchar(max));
insert into @data(instance,array)
values('server1.com',N'[[1613347200,"7"],[1613347205,"6"],[1613347210,"7"]]'),
('server2.com',N'[[1613347200,"5"],[1613347205,"8"],[1613347210,"7"]]');
select d.instance,
json_value(j.value, '$[0]') as date,
json_value(j.value, '$[1]') as value
from @data d
cross apply openjson(d.array) j
order by d.instance,j.[key];____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
If you can count on the values being the same length and/or there are only a very limited number of length variations, you could just SUBSTRING the data from the array, something like this:
SELECT ca1.*
FROM ( VALUES
('server1.com','[[1613347200,"7"],[1613347205,"6"],[1613347210,"7"]]'),
('server2.com','[[1613347200,"5"],[1613347205,"8"],[1613347210,"7"]]') ) AS data(instance, array)
CROSS APPLY (
SELECT instance, SUBSTRING(array, 3, 10) AS date, SUBSTRING(array, 15, 1) AS value
WHERE SUBSTRING(array, 13, 1) = ',' AND SUBSTRING(array, 30, 1) = ','
UNION ALL
SELECT instance, SUBSTRING(array, 20, 10) AS date, SUBSTRING(array, 32, 1) AS value
WHERE SUBSTRING(array, 13, 1) = ',' AND SUBSTRING(array, 30, 1) = ','
UNION ALL
SELECT instance, SUBSTRING(array, 37, 10) AS date, SUBSTRING(array, 49, 1) AS value
WHERE SUBSTRING(array, 13, 1) = ',' AND SUBSTRING(array, 30, 1) = ','
) AS ca1SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
If the array is really JSON then Mark Cowne's answer seems correct. If it's just a delimited string with repeating fixed length segments then something like this could work
declare @data table(instance varchar(20),
array nvarchar(max));
insert into @data(instance,array) values
('server1.com',N'[[1613347200,"7"],[1613347205,"6"],[1613347210,"7"]]'),
('server2.com',N'[[1613347200,"5"],[1613347205,"8"],[1613347210,"7"]]');
select d.instance,
substring(d.array, fn.n*17+3, 10) [date],
substring(d.array, fn.n*17+15, 1) [value]
from @data d
cross apply dbo.fnTally(0, (len(d.array)-1)/17-1) fn;instancedatevalue
server1.com16133472007
server1.com16133472056
server1.com16133472107
server2.com16133472005
server2.com16133472058
server2.com16133472107Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
thank you all for the suggestions going to work through them now
many thanks Simon
-
Sorry... erroneous post removed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry... erroneous post removed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry... erroneous post removed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply