May 15, 2020 at 8:15 am
I have a field in my DB that has a string of JSON data. This is an example:
[{"id":"36653","invoicenumber":"8224131","billinginvoiceid":"948439"},{"id":"36651","invoicenumber":"8224133","billinginvoiceid":"948437"},{"id":"36652","invoicenumber":"8224134","billinginvoiceid":"948438"},{"id":"36654","invoicenumber":"8224155","billinginvoiceid":"948440"},{"id":"36655","invoicenumber":"8224161","billinginvoiceid":"948442"}]
As you can see, there are basically 3 fields: ID, invoicenumber, and billinginvoiceid. This string can have 1 record or 1000 records, there really isn't a limit. I need to be able to query that field to pull the various data elements. So for the above JSON string, I need to query and pull the ID field and the results provide me with a result set that has the 5 ID's you see above. Is this possible? I've been reading and testing with JSON_VALUE function, but I can't seem to make it work. Any help would be greatly appreciated.
Thank you!
Jordon
May 15, 2020 at 9:53 am
Hi Jordon
You should be able to use JSON_VALUE to read just the id field.
The way I have done something like this is to bring json string in a table - one row each for each set - so in your case 5 rows. And then use JSON_VALUE function against that table.
Found the approaches here quite helpful - https://www.sqlservercentral.com/articles/approaches-to-import-json-in-ssis-sql-server-2016-part-1-1
Thanks
May 15, 2020 at 12:28 pm
drop table if exists dbo.test_j;
go
create table dbo.test_j(
j nvarchar(max));
go
insert dbo.test_j(j) values('[{"id":"36653","invoicenumber":"8224131","billinginvoiceid":"948439"},{"id":"36651","invoicenumber":"8224133","billinginvoiceid":"948437"},{"id":"36652","invoicenumber":"8224134","billinginvoiceid":"948438"},{"id":"36654","invoicenumber":"8224155","billinginvoiceid":"948440"},{"id":"36655","invoicenumber":"8224161","billinginvoiceid":"948442"}]');
select j_open.*
from
dbo.test_j j
cross apply
openjson(j.j) with (id int,
invoicenumber bigint,
billinginvoiceid bigint) j_open;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 15, 2020 at 5:04 pm
Thank you both so much for your help! I'm now able to break out this json string!!!
Jordon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy