0

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?

Aamar
  • 31
  • 2
  • the downvotes are probably because you are not saying what you have tried and there are plenty of options from google (or SO https://stackoverflow.com/questions/23723473/query-json-inside-sql-server-2012-column). are you looking for someting better than string processing? –  Nov 01 '17 at 08:08

1 Answers1

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
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52