[SOLVED] formatting json in Coldfusion

formatting json in Coldfusion

I am at a loss on the SerializeJSON output of a CFQUERY. The below code:

<cfsetting showdebugoutput="yes">
<cfheader name="Content-Type" value="application/json">

<cftry>
<cfquery name="GetData" datasource="dsn">
  with cte as (
      select distinct ZipCode from db.dbo.table1 where ZipCode in (#URL.Zip#)
  )
  SELECT
      cte.ZipCode
      , '##' +  CONVERT(VARCHAR(max), CRYPT_GEN_RANDOM(3), 2) as Color
  from cte
</cfquery>

<cfoutput>
#SerializeJSON(GetData)#
</cfoutput>

  <cfcatch type="any">
    Error: <cfoutput>#cfcatch.message#</cfoutput>
  </cfcatch>
</cftry>

creates this output:

{"COLUMNS":["ZIPCODE","COLOR"],"DATA":[["54814","#3269B7"],["60050","#DC3E08"]]}

when the #URL.ZIP# variable = 60050,54814

I need the DATA component to have the JSON formatted with the zip code as a key and the color code as a data value.

How do I tell SerializeJSON I need it that way?

Thanks

Solution

You can you the 2nd argument(queryFormat) in SerializeJSON(data[, queryFormat[, useSecureJSONPrefix[, useCustomSerializer]]])

serializeJSON(GetData, 'STRUCT')

The result will look something like this.

[{"ZIPCODE": "54814","COLOR": "#3269B7"}, {"ZIPCODE": "60050","COLOR": "#DC3E08"}]