JSON String, Extract Value using JSON-Path
JSON String, Extract Value using JSON-Path
Extracts value from JSON using JSON Path. It is used when extracting necessary data from JSON received by the Incoming Webhook function. It is also possible to set the path to get multiple values.
Configs
  • A: Set JSON Text *#{EL}
  • B: Set JSON Path to Extract Value (e.g. “$.id”) *#{EL}
  • C: Select STRING or NUMERIC that stores Extracted Value (update) *
Script (click to open)
// GraalJS Script (engine type: 2)

/*
Notes:
- JSONPath is a query language for JSON, similar to XPath for XML.
    - A JSONPath expression specifies a path to an element (or a set of elements) in a JSON.
    - Paths can use the dot notation: "$.store.book[0].title" or "$['store']['book'][0]['title']"
- JSON is an abbreviation for "JavaScript Object Notation" and a widely used data exchange format.
- Assuming an element set, specify a multiline string type to store extracted value.
- In getting as a number data, numeric parsing depends on JavaScript parseFloat().
    - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/parseFloat
- If the path to the object or array is configured, the value cannot be obtained.
    - Output Sample: "[object Object],[object Object]"
- JSON PATH Online Evaluator
    - http://jsonpath.com/
- Example1
    - { "id": "8PT59XXXXX68743YYYYYCATA", "resource": { "id": "2DC87XXXXX52041YY" } }
    - Config PATH: "$.resource.id"
    - Return Value: "2DC87XXXXX52041YY"
- Example2
      {
        "etag": "abcdefgh",
        "members": [{
          "etag": "ijklmnop",
          "email": "example1@example.com"
        },
        {
          "etag": "qrstuvwx",
          "email": "example2@example.com"
        }]
      }
    - Config PATH: "$.members[*].email"
    - Return Value (required Multiline String pocket):
      example1@example.com
      example2@example.com

Notes(ja):
- JSONPath とは XPath に似た JSON データ用のクエリ言語です。
    - JSONPath 式は、JSON データ内の要素(または要素セット)へのパスを指定します。
    - ドット表記等で設定されます: "$.store.book[0].title" or "$['store']['book'][0]['title']"
- JSON とは "JavaScript Object Notation" の略です。広く普及しているデータ交換フォーマットです。
- 要素セットが想定される場合、格納先には複数行文字列型のデータ項目を指定してください。
- 数値として取得したい(数値型データ項目を指定した)場合、JavaScript parseFloat() で解釈されます。
    - https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/parseFloat
- オブジェクトや配列へのパスが設定された場合、値(Value)は取得できません。
    - 取得例: "[object Object],[object Object]"
*/


/*
APPENDIX
- https://goessner.net/articles/JsonPath/index.html#e2
    - $
    - the root object/element
    - @
    - the current object/element
    - . or []
    - child operator
    - ..
    - recursive descent. JSONPath borrows this syntax from E4X.
    - *
    - wildcard. All objects/elements regardless their names.
    - []
    - subscript operator.
    - XPath uses it to iterate over element collections and for predicates.
    - In Javascript and JSON it is the native array operator.
    - [,]
    - Union operator in XPath results in a combination of node sets.
    - JSONPath allows alternate names or array indices as a set.
    - [start:end:step]
    - array slice operator borrowed from ES4.
    - ?()
    - applies a filter (script) expression.
    - ()
    - script expression, using the underlying script engine.
*/

//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strJsonText      = configs.get( "StrConfA" ); // required
  if( strJsonText    === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A JsonText} is empty \n" );
  }
const strJsonPath      = configs.get( "StrConfB" ); // required
  if( strJsonPath    === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B JsonPath} is empty \n" );
  }
const multiPocketJsonValue = configs.getObject( "SelectConfC" ); // required


//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)


//// == Calculating / 演算 ==
const objJsonText   = JSON.parse( strJsonText );
const arrJsonValues = jsonPath( objJsonText, strJsonPath );
engine.log( " AutomatedTask: # of JsonValues: " + arrJsonValues.length );

let strJsonValues  = "";
for( let i = 0; i < arrJsonValues.length; i++ ){
  strJsonValues   += arrJsonValues[i].toString();
  if( i !== arrJsonValues.length - 1 ){
    strJsonValues += "\n";
  }
}
let numJsonValue   = parseFloat( strJsonValues );


//// == Data Updating / ワークフローデータへの代入 ==
if( multiPocketJsonValue.matchDataType( "STRING" ) ){
  engine.log( " AutomatedTask: Pocket for JsonValue is STRING type" );
  engine.setData( multiPocketJsonValue, strJsonValues );
}else{
  engine.log( " AutomatedTask: Pocket for JsonValue is NUMERIC type" );
  engine.setData( multiPocketJsonValue, new java.math.BigDecimal( numJsonValue ) );
}

} //////// END "main()" /////////////////////////////////////////////////////////////////



////////// (source) https://code.google.com/archive/p/jsonpath/downloads
/* JSONPath 0.8.0 - XPath for JSON
 *
 * Copyright (c) 2007 Stefan Goessner (goessner.net)
 * Licensed under the MIT (MIT-LICENSE.txt) licence.
 */
function jsonPath(obj, expr, arg) {
  var P = {
    resultType: arg && arg.resultType || "VALUE",
    result: [],
    normalize: function(expr) {
      var subx = [];
      return expr.replace(/[\['](\??\(.*?\))[\]']/g, 
                          function($0,$1){return "[#"+(subx.push($1)-1)+"]";})
                 .replace(/'?\.'?|\['?/g, ";")
                 .replace(/;;;|;;/g, ";..;")
                 .replace(/;$|'?\]|'$/g, "")
                 .replace(/#([0-9]+)/g, function($0,$1){return subx[$1];});
    },
    asPath: function(path) {
      var x = path.split(";"), p = "$";
      for (var i=1,n=x.length; i<n; i++)
         p += /^[0-9*]+$/.test(x[i]) ? ("["+x[i]+"]") : ("['"+x[i]+"']");
      return p;
    },
    store: function(p, v) {
      if (p) P.result[P.result.length] = P.resultType == "PATH" ? P.asPath(p) : v;
      return !!p;
    },
    trace: function(expr, val, path) {
      if (expr) {
        var x = expr.split(";"), loc = x.shift();
        x = x.join(";");
        if (val && val.hasOwnProperty(loc))
          P.trace(x, val[loc], path + ";" + loc);
        else if (loc === "*")
          P.walk(loc, x, val, path, function(m,l,x,v,p) { P.trace(m+";"+x,v,p); });
        else if (loc === "..") {
          P.trace(x, val, path);
          P.walk(loc, x, val, path,
                 function(m,l,x,v,p) {
                   typeof v[m] === "object" && P.trace("..;"+x,v[m],p+";"+m);
                 }
          );
        }
        else if (/,/.test(loc)) { // [name1,name2,...]
          for (var s=loc.split(/'?,'?/),i=0,n=s.length; i<n; i++)
            P.trace(s[i]+";"+x, val, path);
        }
        else if (/^\(.*?\)$/.test(loc)) // [(expr)]
          P.trace(P.eval(loc, val, path.substr(path.lastIndexOf(";")+1))+";"+x, val, path);
        else if (/^\?\(.*?\)$/.test(loc)) // [?(expr)]
          P.walk(loc, x, val, path, 
             function(m,l,x,v,p) {
               if (P.eval(l.replace(/^\?\((.*?)\)$/,"$1"),v[m],m)) P.trace(m+";"+x,v,p);
             }
          );
        else if (/^(-?[0-9]*):(-?[0-9]*):?([0-9]*)$/.test(loc))
          // [start:end:step]  phyton slice syntax
          P.slice(loc, x, val, path);
      }
      else
        P.store(path, val);
    },
    walk: function(loc, expr, val, path, f) {
      if (val instanceof Array) {
        for (var i=0,n=val.length; i<n; i++)
          if (i in val)
            f(i,loc,expr,val,path);
      }
      else if (typeof val === "object") {
        for (var m in val)
          if (val.hasOwnProperty(m))
            f(m,loc,expr,val,path);
      }
    },
    slice: function(loc, expr, val, path) {
      if (val instanceof Array) {
        var len=val.length, start=0, end=len, step=1;
        loc.replace(/^(-?[0-9]*):(-?[0-9]*):?(-?[0-9]*)$/g,
            function($0,$1,$2,$3){
              start=parseInt($1||start);end=parseInt($2||end);step=parseInt($3||step);
            });
        start = (start < 0) ? Math.max(0,start+len) : Math.min(len,start);
        end   = (end < 0)   ? Math.max(0,end+len)   : Math.min(len,end);
        for (var i=start; i<end; i+=step)
           P.trace(i+";"+expr, val, path);
      }
    },
    eval: function(x, _v, _vname) {
      try { return $ && _v && eval(x.replace(/@/g, "_v")); }
      catch(e) { throw new SyntaxError("jsonPath: " + e.message + ": " +
                 x.replace(/@/g, "_v").replace(/\^/g, "_a")); }
    }
  };
  var $ = obj;
  if (expr && obj && (P.resultType == "VALUE" || P.resultType == "PATH")) {
    P.trace(P.normalize(expr).replace(/^\$;/,""), obj, "$");
    return P.result.length ? P.result : false;
  }
} 
/// JSONPath end


Download

2021-02-09 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/json-string-extract-value-using-json-path/
The Addon-import feature is available with Professional or Enterprise edition.

Notes

  • JSONPath is a query language for JSON, similar to XPath for XML.
    • A JSONPath expression specifies a path to an element (or a set of elements) in a JSON.
    • Paths can use the dot notation: “$.store.book[0].title” or “$['store']['book'][0]['title']
  • JSON is an abbreviation for “JavaScript Object Notation” and a widely used data exchange format.
  • Assuming an element set, specify a multiline string type to store extracted value.
  • In getting as a number data, numeric parsing depends on JavaScript parseFloat().
  • If the path to the object or array is configured, the value cannot be obtained.
    • Output Sample: “[object Object],[object Object]
  • JSON PATH Online Evaluator
  • Example1
    { "id": "8PT59XXXXX68743YYYYYCATA", "resource": { "id": "2DC87XXXXX52041YY" } }
    • Config PATH: “$.resource.id
    • Return Value: “2DC87XXXXX52041YY
  • Example2
    { "etag": "abcdefgh", "members": [{ "etag": "ijklmnop", "email": "example1@example.com" }, { "etag": "qrstuvwx", "email": "example2@example.com" }] }
    • Config PATH: “$.members[*].email
    • Return Value (required Multiline String pocket):
      example1@example.com
      example2@example.com

Capture

Extracts value from JSON using JSON Path. It is used when extracting necessary data from JSON received by the Incoming Webhook function. It is also possible to set the path to get multiple values.

Appendix

  • https://goessner.net/articles/JsonPath/index.html#e2
  • $
    • the root object/element
  • @
    • the current object/element
  • . or []
    • child operator
  • ..
    • recursive descent. JSONPath borrows this syntax from E4X.
  • *
    • wildcard. All objects/elements regardless their names.
  • []
    • subscript operator.
    • XPath uses it to iterate over element collections and for predicates.
    • In Javascript and JSON it is the native array operator.
  • [,]
    • Union operator in XPath results in a combination of node sets.
    • JSONPath allows alternate names or array indices as a set.
  • [start:end:step]
    • array slice operator borrowed from ES4.
  • ?()
    • applies a filter (script) expression.
  • ()
    • script expression, using the underlying script engine.

See also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: