SQL Server 2014 get data from JSON column

Question

I have a table with many columns, one of them contains JSON structured data, for example:

{"firstname": "john", "lastname": "tim"}

I need to get all the lastname data.

Any suggestions?

Answer 1

Here is one more way for reading the lastname data from JSON :

SELECT REPLACE(REPLACE(REPLACE(DATA, '}', ''), '"lastname":', ''), '"', '') [LastName]
FROM
(
    SELECT split.a.value('.', 'NVARCHAR(MAX)') DATA
    FROM
    (
        SELECT CAST('<A>'+REPLACE(<jsoncolumn>, ',', '</A><A>')+'</A>' AS XML) AS String
        FROM <table_name>
    ) A
    CROSS APPLY String.nodes('/A') AS split(a)
) AA
WHERE AA.DATA LIKE '%lastname%';

Result :

LastName
tim