SQL with JSON

Transact-SQL
1

Hi friends,

I'm a beginner when it comes to JSON and am trying to figure out how to parse this. For example, how would I go about extracting utm_source? It should say facebook in the example below:

[{"key":"utm_campaign","value":"{{campaign.name}}","type":"AD"},{"key":"utm_medium","value":"cpc","type":"AD"},{"key":"utm_source","value":"facebook","type":"AD"},{"key":"utm_content","value":"{{adset.name}}","type":"AD"}]

2

Welcome

Try these

declare @json as varchar(2500) = '[{"key":"utm_campaign","value":"{{campaign.name}}","type":"AD"},{"key":"utm_medium","value":"cpc","type":"AD"},{"key":"utm_source","value":"facebook","type":"AD"},{"key":"utm_content","value":"{{adset.name}}","type":"AD"}]'


SELECT * FROM OPENJSON(@json)
        WITH (  [key] VARCHAR(150),
                value VARCHAR(150),
				type VARCHAR(150)
				) as months;

SELECT * FROM OPENJSON(@json)
        WITH (  value VARCHAR(150) '$.value') as months;

More details here

3

Would it be possible to use the json_value function?

4

You sure can

5

Ok cool. Any idea how to write that to parse out for example, utm_source from above?

6

Try it out based on docu and post back if stuck

7

I took a look at that document but I’m not sure how the syntax works. I posted an example. Would you be able to suggest something?

8 
select JSON_VALUE(@json,'$[2].value');

2 because it is a json array 0 base

9

Thanks for this information.