How can I show the column names in my JSON file format? (Through ColdFusion)

Question

Through the following ColdFusion .cfm file, I am able to get a .json string, which shows me the requested data:

json1.cfm

<cfsetting showdebugoutput="no">
<cfheader name="Content-Type" value="application/json">
<cfquery name="GetData" datasource="myDataSource">
    select distinct ap1, ap2, nombre, idcargo, idencal, telefono, email, cve_nivel, sexo
    FROM vlayout_1
    where cct='13DCC0003S'
</cfquery>

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

Once I upload it on my local server, this is the outcome .json string:

{"COLUMNS":["AP1","AP2","NOMBRE","IDCARGO","IDENCAL","TELEFONO","EMAIL","CVE_NIVEL","SEXO"],"DATA":[["ALVARADO","HERNANDEZ","ALEJANDRO",3,1,"","",5,"M"],["BAUTISTA","OSORIO","ANTONIO",3,5,"","",6,"M"],["HERNANDEZ","ALVARADO","LAURA",3,5,"","",6,"F"],["HERNANDEZ","ANDRADE","MA. TERESA",2,5,"","",6,"F"],["HERNANDEZ","HERNANDEZ","FILOMENA",3,5,"","",4,""],["HERNANDEZ","HERNANDEZ","FILOMENA",3,5,"","",5,""],["HERNANDEZ","HERNANDEZ","MARIA GUADALUPE",3,5,"","",5,"F"],["HERNANDEZ","HERNANDEZ","MARIA LUISA",3,5,"","",4,"F"],["HERNANDEZ","MARTINEZ","MARIA MANUELA",3,5,"","",4,"F"],["HERNANDEZ","QUINTERO","CIRILA",3,5,"","",5,"F"],["LORENZO","LEON","JUAN",3,5,"","",6,"M"],["MARTINEZ","HERNANDEZ","ROSALBA",1,5,"","",5,"F"],["SIXTO","RAMIREZ","EUTIQUIO",3,5,"","",4,"M"],["SIXTO","RAMIREZ","EUTIQUIO",3,5,"","",5,"M"]]}

After I validate on http://jsonlint.com/ this is what I get:

{
    "COLUMNS": [
        "AP1",
        "AP2",
        "NOMBRE",
        "IDCARGO",
        "IDENCAL",
        "TELEFONO",
        "EMAIL",
        "CVE_NIVEL",
        "SEXO"
    ],
    "DATA": [
        [
            "ALVARADO",
            "HERNANDEZ",
            "ALEJANDRO",
            3,
            1,
            "",
            "",
            5,
            "M"
        ],
        [
            "BAUTISTA",
            "OSORIO",
            "ANTONIO",
            3,
            5,
            "",
            "",
            6,
            "M"
        ],
        [
            "HERNANDEZ",
            "ALVARADO",
            "LAURA",
            3,
            5,
            "",
            "",
            6,
            "F"
        ],
        [
            "HERNANDEZ",
            "ANDRADE",
            "MA. TERESA",
            2,
            5,
            "",
            "",
            6,
            "F"
        ],
        [
            "HERNANDEZ",
            "HERNANDEZ",
            "FILOMENA",
            3,
            5,
            "",
            "",
            4,
            ""
        ],
        [
            "HERNANDEZ",
            "HERNANDEZ",
            "FILOMENA",
            3,
            5,
            "",
            "",
            5,
            ""
        ],
        [
            "HERNANDEZ",
            "HERNANDEZ",
            "MARIA GUADALUPE",
            3,
            5,
            "",
            "",
            5,
            "F"
        ],
        [
            "HERNANDEZ",
            "HERNANDEZ",
            "MARIA LUISA",
            3,
            5,
            "",
            "",
            4,
            "F"
        ],
        [
            "HERNANDEZ",
            "MARTINEZ",
            "MARIA MANUELA",
            3,
            5,
            "",
            "",
            4,
            "F"
        ],
        [
            "HERNANDEZ",
            "QUINTERO",
            "CIRILA",
            3,
            5,
            "",
            "",
            5,
            "F"
        ],
        [
            "LORENZO",
            "LEON",
            "JUAN",
            3,
            5,
            "",
            "",
            6,
            "M"
        ],
        [
            "MARTINEZ",
            "HERNANDEZ",
            "ROSALBA",
            1,
            5,
            "",
            "",
            5,
            "F"
        ],
        [
            "SIXTO",
            "RAMIREZ",
            "EUTIQUIO",
            3,
            5,
            "",
            "",
            4,
            "M"
        ],
        [
            "SIXTO",
            "RAMIREZ",
            "EUTIQUIO",
            3,
            5,
            "",
            "",
            5,
            "M"
        ]
    ]
}

The question is, how can I show every column name before its content? e.g.:

 [
                AP1:"SIXTO",
                AP2:"RAMIREZ",
                NOMBRE:"EUTIQUIO",
                IDCARGO:3,
                IDENCAL:5,
                TELEFONO:"",
                EMAIL:"",
                CVE_NIVEL:5,
                SEXO:"M"
            ]

Thank you so much in advance!

Isn't that the same as what [you posted here](http://stackoverflow.com/questions/16720931/how-can-i-get-this-kind-of-json-file-format-from-a-sql-query)? Did you try any of the suggestions in the referenced links? If you ran into problems, you should [edit the question](http://stackoverflow.com/posts/16720931/edit), rather than open a new one. — Leigh, May 23 '13 at 22:13
Hi, @Leigh. Actually the [previous question](http://stackoverflow.com/questions/16720931/how-can-i-get-this-kind-of-json-file-format-from-a-sql-query) was about how to get each "individual" row of my query. Thanks to your [your answer](http://stackoverflow.com/questions/15840003/using-json-data-with-coldfusion/15841047#15841047) I was able to do that. (Thank you, by the way). You are right, I could have edited the original question instead of creating a new one, and I apologize for that. Happily, now I can see the column name, thanks to the answer from imthepitts. — mx1810, May 24 '13 at 15:42
Ah, so you had worked out how to do it manually but were really asking how to get *all* the column names dynamically? (That part was not clear from the OP ;-). Anyway, glad you got it working. — Leigh, May 24 '13 at 16:18

score 2 · Accepted Answer · answered May 23 '13 at 22:04

You'll need to convert it to an array of structures, then apply serializeJSON().

<cfsetting showdebugoutput="no">
<cfheader name="Content-Type" value="application/json">
<cfquery name="GetData" datasource="myDataSource">
    select distinct ap1, ap2, nombre, idcargo, idencal, telefono, email, cve_nivel, sexo
    FROM vlayout_1
    where cct='13DCC0003S'
</cfquery>

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


<cfscript>
/**
 * @hint Converts a query to an array of structures.
 */
public array function queryToArray(required query query){
    var result = [];
    for (var i = 1; i <= query.recordCount; i++){
        arrayAppend(result, queryToStruct(query, i));
    }
    return result;
}

/**
 * @hint Returns the first row of a query as a structure with same case as query column names.
 */
public struct function queryToStruct(required query query, numeric rowNumber=1){

    var struct = {};
    var columns = arguments.query.getMeta().getColumnLabels();

    for (var i = 1; i <= arrayLen(columns); i++){
        struct[columns[i]] = query[columns[i]][arguments.rowNumber];
    }

    return struct;
}
</cfscript>

Agreed, i do despise having todo this, i wish cf returntype json made json in typical json format — Jay Rizzi, May 24 '13 at 01:19
@imthepitts you are the man! Your answer allowed me to do exactly what I needed. You don't know how much I appreciate your help. **Thank you!** — mx1810, May 24 '13 at 15:52
Glad it worked out, @mx1810. Just be careful about using this on very large recordsets, as it doesn't perform well at that scale. Best to use built-in queries in those cases. — imthepitts, May 27 '13 at 17:58

How can I show the column names in my JSON file format? (Through ColdFusion)

1 Answers1

Linked