Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

query result to JSON object (using javascript action)

Avatar

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

Avatar

Correct answer by
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

Avatar

Correct answer by
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

Avatar

Level 2

Hi,

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

Cheers

Avatar

Level 6

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

Avatar

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