I'm trying to parse this sting of data...its an field coming from an API GET...

{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}

It looks familiar. Like a JSON hash in ruby...

What would be the best method to parse this. I have the data in a SQL Server table so TSQL preferable. But I would also consider Ruby or Python if it was better..

CodePudding user response：

You may try to transform and parse the stored API data as JSON.

Sample data:

SELECT *
INTO Data
FROM (VALUES
   ('{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}'),
   ('Invalid JSON data')
) v (ApiData)

Statement:

SELECT t.ApiData, j.*
FROM (
   SELECT 
      ApiData,
      JsonData = CASE
         WHEN ISJSON(REPLACE(ApiData, '=>', ':')) = 1 THEN REPLACE(ApiData, '=>', ':')
         ELSE NULL
      END   
   FROM Data
) t  
OUTER APPLY OPENJSON(t.JsonData) j

Result: