Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

query result to JSON object (using javascript action)

RaulOcana
Level 4
Level 4

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

1 Accepted Solution
Jonathon_wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

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

Thanks,

-Jon

View solution in original post

4 Replies
Jonathon_wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

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

Thanks,

-Jon

View solution in original post

bjoernkoth
Level 2
Level 2

Hi,

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

Cheers

Florian_Courgey
Community Advisor
Community Advisor

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
Level 2
Level 2

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