2

You see, this is my issue:

I need to get a .json file from a SQL query. In order to do that, I use the following ColdFusion .cfm file:

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

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

Then, I save this file in my localhost server and this is the result:

{"ROWCOUNT":12,"COLUMNS":["AP1","AP2","NOMBRE","IDCARGO"],"DATA":{"AP1":["ALVARADO","BAUTISTA","HERNANDEZ","HERNANDEZ","HERNANDEZ","HERNANDEZ","HERNANDEZ","HERNANDEZ","HERNANDEZ","LORENZO","MARTINEZ","SIXTO"],"AP2":["HERNANDEZ","OSORIO","ALVARADO","ANDRADE","HERNANDEZ","HERNANDEZ","HERNANDEZ","MARTINEZ","QUINTERO","LEON","HERNANDEZ","RAMIREZ"],"NOMBRE":["ALEJANDRO","ANTONIO","LAURA","MA. TERESA","FILOMENA","MARIA GUADALUPE","MARIA LUISA","MARIA MANUELA","CIRILA","JUAN","ROSALBA","EUTIQUIO"],"IDCARGO":[3,3,3,2,3,3,3,3,3,3,1,3]}}

Once I validate it and format it to JSON through http://jsonlint.com/, this is what I obtain:

{
    "ROWCOUNT": 12,
    "COLUMNS": [
        "AP1",
        "AP2",
        "NOMBRE",
        "IDCARGO"
    ],
    "DATA": {
        "AP1": [
            "ALVARADO",
            "BAUTISTA",
            "HERNANDEZ",
            "HERNANDEZ",
            "HERNANDEZ",
            "HERNANDEZ",
            "HERNANDEZ",
            "HERNANDEZ",
            "HERNANDEZ",
            "LORENZO",
            "MARTINEZ",
            "SIXTO"
        ],
        "AP2": [
            "HERNANDEZ",
            "OSORIO",
            "ALVARADO",
            "ANDRADE",
            "HERNANDEZ",
            "HERNANDEZ",
            "HERNANDEZ",
            "MARTINEZ",
            "QUINTERO",
            "LEON",
            "HERNANDEZ",
            "RAMIREZ"
        ],
        "NOMBRE": [
            "ALEJANDRO",
            "ANTONIO",
            "LAURA",
            "MA. TERESA",
            "FILOMENA",
            "MARIA GUADALUPE",
            "MARIA LUISA",
            "MARIA MANUELA",
            "CIRILA",
            "JUAN",
            "ROSALBA",
            "EUTIQUIO"
        ],
        "IDCARGO": [
            3,
            3,
            3,
            2,
            3,
            3,
            3,
            3,
            3,
            3,
            1,
            3
        ]
    }
}

The issue is that they asked me this .json format:

actasPrimeraAsamblea:{
    [
        ceIntegrante:[
                   {paterno:HERNANDEZ,
                         materno:MARTÍNEZ,
                         nombre:MARÍA GUADALUPE,
                         idcargo:3},
                       {paterno:PÉREZ,
                         materno:ROSALES,
                         nombre:JOSÉ,
                         idcargo:3},
                       {paterno:RAMÍREZ,
                         materno:GONZÁLEZ,
                         nombre:MARIO,
                         idcargo:3}

                     ]
    ]
}

Can you note the differences? My original .json format shows me the View columns, and I need it to be combined.

How can I achieve this?

Thank you in advance, it has been really frustrating.

Sorry for my poor english.

mx1810
  • 65
  • 6
  • You simply need to format the data returned from the query into the structure you want before serializing it. Have you made any attempt at all to do this? – Mike Brant May 23 '13 at 18:13
  • Check out this post here: http://iknowkungfoo.com/blog/index.cfm/2012/5/11/ArrayCollectioncfc-a-custom-JSON-renderer-for-ColdFusion-queries – steve May 23 '13 at 18:23
  • Hi, @MikeBrant. I haven't made any attempt, any tip to do that? (Thanks for your answer) – mx1810 May 23 '13 at 18:51
  • @user1411152 Well you should make an attempt and come back and post questions if you run into specific problems. SO is not really expected to be a place where people will write your code for you. – Mike Brant May 23 '13 at 19:39
  • *they asked me this .json format:* First, that does not look like a valid JSON string. Second, like @Mike said, it is just a matter of organizing the query data into [nested CF arrays and structures](http://stackoverflow.com/questions/15840003/using-json-data-with-coldfusion/15841047#15841047). Then serializing them. Take a look at the link above to see how queries can be converted into "arrays of structures". (See also simplified example at [the very end of this post](http://stackoverflow.com/a/15124500/104223)). Give it a whirl first. Then post back if you run into a specific problem. – Leigh May 23 '13 at 19:49
  • Thank you, @steve! Amazing post! I will read it in detail. – mx1810 May 23 '13 at 20:26
  • Yes, I am working on that. Now I know that the key is "to structure" the data before serializing it. Thanks to the link that @steve posted, I have a clearer panorama. – mx1810 May 23 '13 at 20:31

0 Answers0