Post reply

Query JSON data

  • 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

  • May 15, 2020 at 9:53 am

    #3751940

    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://qa.sqlservercentral.com/articles/approaches-to-import-json-in-ssis-sql-server-2016-part-1-1

    Thanks

     

     

  • May 15, 2020 at 12:28 pm

    #3751959 

    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

    #3752073

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

    Jordon

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

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