Databases
StackOverflow2013
Postsdbo
POHow can I get this kind of JSON file format from a SQL query?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I get this kind of JSON file format from a SQL query?
    text
    copied!<p>You see, this is my issue:</p> <p>I need to get a .json file from a SQL query. In order to do that, I use the following ColdFusion .cfm file:</p> <pre><code>&lt;cfsetting showdebugoutput="no"&gt; &lt;cfheader name="Content-Type" value="application/json"&gt; &lt;cfquery name="GetData" datasource="myDataSource"&gt; select distinct ap1, ap2, nombre, idcargo FROM vlayout_1 where cct='13DCC0003S' &lt;/cfquery&gt; &lt;cfoutput&gt; #SerializeJSON(GetData, true)# &lt;/cfoutput&gt; </code></pre> <p>Then, I save this file in my localhost server and this is the result:</p> <pre><code>{"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]}} </code></pre> <p>Once I validate it and format it to JSON through <a href="http://jsonlint.com/" rel="nofollow">http://jsonlint.com/</a>, this is what I obtain:</p> <pre><code>{ "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 ] } } </code></pre> <p>The issue is that they asked me this .json format:</p> <pre><code>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} ] ] } </code></pre> <p>Can you note the differences? My original .json format shows me the View columns, and I need it to be combined.</p> <p>How can I achieve this?</p> <p>Thank you in advance, it has been really frustrating.</p> <p>Sorry for my poor english.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload