query result to JSON object (using javascript action)

[RaulOcana]

Hi guys,

It is possible to convert from a query result to a JSON object?

This is the query:

var query = xtk.queryDef.create(

  <queryDef schema="search:country" operation="select">

  <where>

       <condition expr={"@codigo_iata='" + iataCode + "'"}/>

    </where>

  </queryDef>

);

query.SelectAll(false);

result = query.ExecuteQuery();

The result is:

<country-collection>   <country codigo_iata="CO" id_pais="1" id_region="5" nombre_aleman="Kolumbien" nombre_espanol="Colombia" nombre_ingles="Colombia" nombre_portugues="Colômbia"/> </country-collection>

Thank you!

Regards,

Raúl

[Jonathon_wodnicki · Accepted solution]

Hi,

You can just define the object by iterating over the result with flatMap or similar, then serialize with JSON.stringify().

Thanks,

-Jon

[bjoernkoth]

Hi,

do we have a working example for this? I wonder why this is not in the basic toolset of AC.

Cheers

[Florian_Courgey]

Hi,

You can use the following for loops:

var query = NLWS.xtkQueryDef.create(
  <queryDef schema="search:country" operation="select">
  <where>
       <condition expr={"@codigo_iata='" + iataCode + "'"}/>
    </where>
  </queryDef>
);
query.SelectAll(false);
var results = query.ExecuteQuery();
for each(var result in results.getElements()){
  logInfo(result.$id_region);
}

And the following for 1 record:

var query = NLWS.xtkQueryDef.create(
  <queryDef schema="search:country" operation="get">
  <where>
       <condition expr={"@codigo_iata='" + iataCode + "'"}/>
    </where>
  </queryDef>
);
query.SelectAll(false);
var result = query.ExecuteQuery();
logInfo(result.$id_region);

Regards

[bjoernkoth]

We came up with a helper function like this

/**
 * Transforms an ExecuteQuery result into a JS Object resp. JS Object array
 * @param {XMLDocument} queryResult a query result
 * @param {string} schema the schema name without namespace, e.g. "recipient" instead of "nms:recipient"
 * @param {[string]} columns the column names to pull data from and map into the result
 * @returns {[object]} an array of JS objects
 */
function queryResultToJS(queryResult, schema, columns) {
    /**
     * Map a given query result item to a JS object
     * @param {object} resultEntry the item mapped to a JS object
     */
    function mapItemToObject(resultEntry) {
        var result = {};
        for (var i = 0; i < columns.length; i++) {
            result[columns[i]] = resultEntry.getAttribute(columns[i]);
        }
        return result;
    }
    var result = [];
    try {
        if (queryResult) {
            if (queryResult.tagName === schema + '-collection') {
                // items in collection or single result
                for each(var collectionItem in queryResult.getElementsByTagName(schema)) {
                    // logInfo('item', JSON.stringify(collectionItem));
                    result.push(mapItemToObject(collectionItem));
                }
            } else if (queryResult.tagName === schema) {
                // single result
                result.push(mapItemToObject(queryResult));
            }
        }
    } catch (error) {
        logError('unable to transform result into JS', error);
    }
    return result;
}

and call it like this

var queryResult = query.ExecuteQuery(),
    deliveries = queryResultToJS(queryResult, "delivery", ["id", "label", "clicks"]);

so basically passing in the columns in the result entries.

The result is always an Array of JS objects, event if the result is just a single entry.

Hope that helps,

Björn