Topic
  • 10 replies
  • Latest Post - ‏2015-08-21T08:33:02Z by HermannSW
Rohit-Goyal
Rohit-Goyal
151 Posts

Pinned topic XQuery/JSONiq - JSON to JSON transformation

‏2014-05-09T01:05:33Z |

I am trying to do JSON to JSON transformation using XQuery/JSONiq. Can anyone help me get XQuery for this transformation?

Input

{
    "name": "John Smith",
    "sku": "20223",
    "price": 23.95,
    "shipTo": {
        "name": "Jane Smith",
        "address": "123 Maple Street"
    },
    "billTo": {
        "name": "John Smith",
        "address": "123 Maple Street"
    }
}

Output that I am looking for is

{
    "name": "John Smith",
    "sku": "20223",
    "price": 23.95,
    "shipTo-name":"Jane Smith",
    "shipTo-address": "123 Maple Street",
    "billTo-name": "John Smith",
    "billTo-address": "123 Maple Street"
}

If possible, please also guide some link to learn JSONiq.

Regards

Rohit Goyal

  • HermannSW
    HermannSW
    6065 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-09T08:55:09Z  

    Hi,

    here you can see that JSONiq script bill.xq does its job (order is only relevant for arrays, not for objects):

    And this is JSONiq script bill.xq (attached too):

     

    DataPower implements version 0.4.42 of the spec, find (good) PDF under "JSONiq Extension to XQuery" tab here:
    http://www.jsoniq.org/

     

     

    http://www-01.ibm.com/support/docview.wss?uid=swg21633648

     

    https://www.ibm.com/developerworks/community/blogs/HermannSW/tags/jsoniq

     

    Forum software kicked me out 5 times in trying to add more content to this.

    Please work with attached solution for now, will add more leaning information later, sorry

     

    Hermann <myBlog/> <myTweets/> | <GraphvizFiddle/> | <xqib/> | <myCE/> <myFrameless/>

    Attachments

    Updated on 2014-05-09T09:30:17Z at 2014-05-09T09:30:17Z by HermannSW
  • Rohit-Goyal
    Rohit-Goyal
    151 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-09T11:12:03Z  
    • HermannSW
    • ‏2014-05-09T08:55:09Z

    Hi,

    here you can see that JSONiq script bill.xq does its job (order is only relevant for arrays, not for objects):

    And this is JSONiq script bill.xq (attached too):

     

    DataPower implements version 0.4.42 of the spec, find (good) PDF under "JSONiq Extension to XQuery" tab here:
    http://www.jsoniq.org/

     

     

    http://www-01.ibm.com/support/docview.wss?uid=swg21633648

     

    https://www.ibm.com/developerworks/community/blogs/HermannSW/tags/jsoniq

     

    Forum software kicked me out 5 times in trying to add more content to this.

    Please work with attached solution for now, will add more leaning information later, sorry

     

    Hermann <myBlog/> <myTweets/> | <GraphvizFiddle/> | <xqib/> | <myCE/> <myFrameless/>

    Thanks Herman. I do not have access to DataPower. But I tried to run the script in Zorba with few changes. It worked but not as expected.

    http://try.zorba.io/queries/xquery/U7s0AoHOJTHV%2BOAPtHLx6jDu3Nw%3D

    I added input JSON as variable. In for loop, used the variable $m instead "."

    The problem I think is with typeswitch statement.. Zorba is not supporting (.($k)) expression. I assume it means finding the value of the key. I am not sure how this expression should look like in Zorba. 

    Can you please have a look and suggest if you see anything wrong?

    Regards

    Rohit Goyal

     

  • Rohit-Goyal
    Rohit-Goyal
    151 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-09T11:16:13Z  

    Thanks Herman. I do not have access to DataPower. But I tried to run the script in Zorba with few changes. It worked but not as expected.

    http://try.zorba.io/queries/xquery/U7s0AoHOJTHV%2BOAPtHLx6jDu3Nw%3D

    I added input JSON as variable. In for loop, used the variable $m instead "."

    The problem I think is with typeswitch statement.. Zorba is not supporting (.($k)) expression. I assume it means finding the value of the key. I am not sure how this expression should look like in Zorba. 

    Can you please have a look and suggest if you see anything wrong?

    Regards

    Rohit Goyal

     

    Opps.. I got it worked in Zorba.. 

    http://try.zorba.io/queries/xquery/InDt3lnnONjEiVpDkdSsg9Y7tDQ%3D

    Thanks Herman. I will check the PDF.

    Regards

    Rohit Goyal

  • HermannSW
    HermannSW
    6065 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-09T11:22:51Z  

    Opps.. I got it worked in Zorba.. 

    http://try.zorba.io/queries/xquery/InDt3lnnONjEiVpDkdSsg9Y7tDQ%3D

    Thanks Herman. I will check the PDF.

    Regards

    Rohit Goyal

    Nice, the "Share" Zorba link you provided works, but it complains on jn:object although it does work, and on that $K is unused variable which it is not ... ;-)

  • HermannSW
    HermannSW
    6065 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-09T12:13:56Z  
    • HermannSW
    • ‏2014-05-09T11:22:51Z

    Nice, the "Share" Zorba link you provided works, but it complains on jn:object although it does work, and on that $K is unused variable which it is not ... ;-)

    Some explanations to bill.xq attached above:

    declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";
    
    declare option jsoniq-version "0.4.42";
    declare option output:method "json";
    
    jn:object(
      for $k in jn:keys(.) return
        typeswitch (.($k))
          case $o as object() return
            for $K in jn:keys($o) return           
              { concat($k,"-",$K) : $o($K) }
    
          default $v return 
            { $k : $v }
    )
    


    output:method "json"  allows to output JSON result

    jn:object()  constructs a new object with dynamic key:value pairs

    typeswitch($v)  allows to handle an object differently by $v type

    case $o as object()  is the line being executed in case $v is an object ($o in that case)

    { "key1" : "value1", ..., "keyN" : "valueN" }  constructs a new object with fixed number of pairs

    $o($K)  returns the value stored under key $K in object $o

    Hermann <myBlog/> <myTweets/> | <GraphvizFiddle/> | <xqib/> | <myCE/> <myFrameless/>

    Updated on 2014-05-09T12:18:34Z at 2014-05-09T12:18:34Z by HermannSW
  • HermannSW
    HermannSW
    6065 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-09T12:49:02Z  
    • HermannSW
    • ‏2014-05-09T12:13:56Z

    Some explanations to bill.xq attached above:

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization"; declare option jsoniq-version "0.4.42"; declare option output:method "json"; jn:object( for $k in jn:keys(.) return typeswitch (.($k)) case $o as object() return for $K in jn:keys($o) return { concat($k,"-",$K) : $o($K) } default $v return { $k : $v } ) </pre>


    output:method "json"  allows to output JSON result

    jn:object()  constructs a new object with dynamic key:value pairs

    typeswitch($v)  allows to handle an object differently by $v type

    case $o as object()  is the line being executed in case $v is an object ($o in that case)

    { "key1" : "value1", ..., "keyN" : "valueN" }  constructs a new object with fixed number of pairs

    $o($K)  returns the value stored under key $K in object $o

    Hermann <myBlog/> <myTweets/> | <GraphvizFiddle/> | <xqib/> | <myCE/> <myFrameless/>

    $ coproc2 bill.xq bill.json http://dp2-l3:2226 ; echo
    
    {
      "shipTo-address":"123 Maple Street",
      "shipTo-name":"Jane Smith",
      "sku":"20223",
      "name":"John Smith",
      "billTo-address":"123 Maple Street",
      "price":23.95,
      "billTo-name":"John Smith"
    }
    $
    

     

  • Rohit-Goyal
    Rohit-Goyal
    151 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-11T12:58:16Z  
    • HermannSW
    • ‏2014-05-09T12:13:56Z

    Some explanations to bill.xq attached above:

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization"; declare option jsoniq-version "0.4.42"; declare option output:method "json"; jn:object( for $k in jn:keys(.) return typeswitch (.($k)) case $o as object() return for $K in jn:keys($o) return { concat($k,"-",$K) : $o($K) } default $v return { $k : $v } ) </pre>


    output:method "json"  allows to output JSON result

    jn:object()  constructs a new object with dynamic key:value pairs

    typeswitch($v)  allows to handle an object differently by $v type

    case $o as object()  is the line being executed in case $v is an object ($o in that case)

    { "key1" : "value1", ..., "keyN" : "valueN" }  constructs a new object with fixed number of pairs

    $o($K)  returns the value stored under key $K in object $o

    Hermann <myBlog/> <myTweets/> | <GraphvizFiddle/> | <xqib/> | <myCE/> <myFrameless/>

    Thank you Herman

    Can you please tell which of these statement only applicable for JSONiq extension for DataPower supportable XQuery? 

    After reading few other docs I understand that JSONiq support in DataPower may be different to what is available in Zorba or 28.io. 

    I am exploring JSONiq to do heavy JSON to JSON transformation. I understand it's a bit learning curve but I am not quite sure if its Ok to learn JSONiq without DataPower box and then try same to DataPower.

    Regards

    Rohit

  • HermannSW
    HermannSW
    6065 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2014-05-11T18:19:41Z  

    Thank you Herman

    Can you please tell which of these statement only applicable for JSONiq extension for DataPower supportable XQuery? 

    After reading few other docs I understand that JSONiq support in DataPower may be different to what is available in Zorba or 28.io. 

    I am exploring JSONiq to do heavy JSON to JSON transformation. I understand it's a bit learning curve but I am not quite sure if its Ok to learn JSONiq without DataPower box and then try same to DataPower.

    Regards

    Rohit

    Hi,

    as said above, we have implemented version 0.4.42 of JSONiq spec:
    http://www.jsoniq.org/docs/JSONiqExtensionToXQuery/pdf/Language_Specification-0.4.42-JSONiq-en-US.pdf

    So as long as you use cosntructs from that spec you should be fine.
    (May not be possible if other JSONiq processor does not support anymore)

    Be aware that optional JSON update functionality has not been implemented (chapter 7).

     

    Hermann <myBlog/> <myTweets/> | <GraphvizFiddle/> | <xqib/> | <myCE/> <myFrameless/>


     

  • Samn
    Samn
    1 Post

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2015-08-20T21:32:07Z  

    can someone help me with why this code doesn't work in datapower 7.1.0.4

    declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";
    declare namespace dp = "http://www.datapower.com/extensions";

    declare option output:method "json"; 
    declare option jsoniq-version "0.4.42";

     let $object1 := { "Captain" : "Kirk" }
     let $object2 := { "First officer" : "Spock" }
     return jn:object($object1, $object2)

     

    it fails with error "[XPST0017] The processor is unable to resolve the call to function "{http://jsoniq.org/functions}object/2"."

    The JSONiq specification for 0.4 says it is valid but it fails in datapower.

    basically my requirement is merge 2 objects.

     

    Thanks,

    Sam.

  • HermannSW
    HermannSW
    6065 Posts

    Re: XQuery/JSONiq - JSON to JSON transformation

    ‏2015-08-21T08:33:02Z  
    • Samn
    • ‏2015-08-20T21:32:07Z

    can someone help me with why this code doesn't work in datapower 7.1.0.4

    declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";
    declare namespace dp = "http://www.datapower.com/extensions";

    declare option output:method "json"; 
    declare option jsoniq-version "0.4.42";

     let $object1 := { "Captain" : "Kirk" }
     let $object2 := { "First officer" : "Spock" }
     return jn:object($object1, $object2)

     

    it fails with error "[XPST0017] The processor is unable to resolve the call to function "{http://jsoniq.org/functions}object/2"."

    The JSONiq specification for 0.4 says it is valid but it fails in datapower.

    basically my requirement is merge 2 objects.

     

    Thanks,

    Sam.

    Hi,

    the error message is correct, and the sample in spec is wrong:
    http://www.jsoniq.org/docs/JSONiqExtensionToXQuery/pdf/Language_Specification-0.4.42-JSONiq-en-US.pdf#page=29

    You have to pass one argument, an "object()*", and not two.

    Create "object()*" from both objects by XQuery "(...)" operator, that works without problems:

     return jn:object( ($object1, $object2) )
    


     

    $ coproc2 spock.xq dummy.xml http://dp3-l3:2225; echo
    
    {
      "Captain":"Kirk",
      "First officer":"Spock"
    }
    $
    


    Hermann.

    Updated on 2015-08-21T08:33:54Z at 2015-08-21T08:33:54Z by HermannSW