can you count the letter n in:
q: data extension data to json with sql im quite new to sql and ive hit a road block in my knowledge. i am using automation studio to write a sql query that takes the data from a data extension and transforms it into json. the idea will be to push the json data to another part of the business later on.
so i have written the following query:
select 

id as subscriberkey,
first_name as firstname,
last_name as lastname,
email as emailaddress,
phone as mobilephone,
cover_type as [need cover for]

from [health_affiliate_email_capture]
for json path 

problem is i dont know how to tell the query to put the json into a field called data that is in the same table.
health_affiliate_email_capture will be capturing data that comes in from a form.
can someone please tell me what i am missing?

a: you just need to give the output of your query a column alias, data.
select (
    select 
        id as [subscriberkey],
        first_name as [firstname],
        last_name as [lastname],
        email as [emailaddress],
        phone as [mobilephone],
        cover_type as [need cover for]
    from
        [health_affiliate_email_capture]
    for json path 
) as [data]

note: query studio uses its own layer of crude syntax checking in order to create the results data extension and will reject this query. automation studios happy enough to run this type of query.

a: not sure a query is the best way to do this. you could use the rest api to get de rows as json .
another option is lookuprows() in ssjs which will return an array of objects for each row. you could stringify the result and http to external system if that’s your end goal.

user
I counted seventy-one "n"s in that text.
assistant