Post reply

Query with a column containing JSON text

  • September 16, 2021 at 3:37 am

    #3930013

    Hi

    I have a table MyFiles with columns Id, FileName, FolderName, KeyPairs. The last one (nvarchar(MAX)) contains JSON text like this:

    {"Licence No ": "12345", "ID Number ": "345678", "Class ": "1AC ", "Card Number ": "6 666 666 666 ", "Expiry Date ": "22 DEC 2016 "}

    I need to write a query which will return the following data:

    Id, FileName, FolderName, [Licence No], Class, [Expiry Date]

    with a filter where [Expiry Date] is greater than the current date.

    If any of  [Licence No], Class, [Expiry Date] is not present in KeyPairs column, it should return NULL for that column.

     

    Thanks.

  • September 16, 2021 at 9:10 am

    #3930075 

    select t.Id, t.FileName, t.FolderName,
           kp.LicenceNo, kp.IDNumber, kp.Class, kp.CardNumber, kp.ExpiryDate
    from MyFiles t
    outer apply openjson(t.KeyPairs) 
    with (LicenceNo int '$."Licence No "',
          IDNumber int  '$."ID Number "',
          Class varchar(10)  '$."Class "',
          CardNumber varchar(20)  '$."Card Number "',
          ExpiryDate date  '$."Expiry Date "' ) kp;

    ____________________________________________________

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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