Query with a column containing JSON text

  • 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.



  • 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


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

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