Query JSON data

Question

Post reply

Query JSON data

jordon.shaw

Hall of Fame

Points: 3571
More actions
May 15, 2020 at 8:15 am

#3751924
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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

@Taps SSC Eights! Points: 879 More actions · Answer 1

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 3

Thank you both so much for your help! I'm now able to break out this json string!!!

Jordon