[New Plugin] Simple JSONata : easily query and transform your JSON

[florian.briand]

This plugin allow to use JSONata syntax to query or transform a JSON.

I made it to perform operations on complex data structures. For example, if I have list of values (from inputs in Repeating Group for example), I use JSON Machine to store all the data, then perform JSONata query/transformation to, for example, sum the values.

More examples are available on the demo page.

It is very usefull combined with other JSON plugins like JSON Machine.
This plugin offers a better stability and a simpler usage than already existing JSONata plugin.

---

Plugin page : Simple JSONata Plugin | Bubble
Demo page : Bubble | No-code apps

[redvivi]

Hi @florian.briand !

Great plugin you have here ! I have a question though.
I have this object representing a table:

[
   {
      "1":{
         "1":"",
         "2":"Agree ",
         "3":"Neutral ",
         "4":"Disagree "
      },
      "2":{
         "1":"Good Service ",
         "2":"X ",
         "3":"",
         "4":""
      },
      "3":{
         "1":"Easy to Use ",
         "2":"",
         "3":"X ",
         "4":""
      },
      "4":{
         "1":"Fair Price ",
         "2":"X ",
         "3":"",
         "4":""
      }
   }
]

Here is how it is structured:

  "1":{  <—— Row Index, may vary from 1 to n
     “1":"",  <—- Column index, may vary from 1 to m, empty cell
     "2":"Agree ", <—- Column index, may vary from 1 to m, and cell containing "Agree"
     "3":"Neutral ",
     "4":"Disagree "
  },

I would like to reconstruct this kind of table on my BubbleApp, of course the table may have n rows and m columns, so it varies.

Any clue on how to do it with your plugin ?

[florian.briand]

I’m no sure to understand exactly your need and constraints.

I see you have a strange nested schema (you seem to have an array at the root level, containing only 1 object, itself containing an object which items are the “rows” you’re looking for).

To access the array at the root level, you have to use $[0] (to get the first array item) or $[] (to just flatten the array).
So you can transform your initial structure into an array ob objects with the formula $[].* which will give you this result :

[
  {
    "1": "",
    "2": "Agree ",
    "3": "Neutral ",
    "4": "Disagree "
  },
  {
    "1": "Good Service ",
    "2": "X ",
    "3": "",
    "4": ""
  },
  {
    "1": "Easy to Use ",
    "2": "",
    "3": "X ",
    "4": ""
  },
  {
    "1": "Fair Price ",
    "2": "X ",
    "3": "",
    "4": ""
  }
]

And if you want to keep the Row Index in the result, you can do this :

$[].$keys().$merge([$lookup($$[], $), {"ID": $}])

Which will give you :

[
  {
    "1": "",
    "2": "Agree ",
    "3": "Neutral ",
    "4": "Disagree ",
    "ID": "1"
  },
  {
    "1": "Good Service ",
    "2": "X ",
    "3": "",
    "4": "",
    "ID": "2"
  },
  {
    "1": "Easy to Use ",
    "2": "",
    "3": "X ",
    "4": "",
    "ID": "3"
  },
  {
    "1": "Fair Price ",
    "2": "X ",
    "3": "",
    "4": "",
    "ID": "4"
  }
]

[redvivi]

Merci @florian.briand !

@florian.briand

I’m no sure to understand exactly your need and constraints.

And apologies for the lack of precision.

So, from the JSON I pasted before, I would like to dynamically generate this on my App (except the check boxes, simple X’s would do):

Could RepeatingGroups do the trick with your plugin ?

[florian.briand]

This Simple JSONata plugin will allow you to easily transform your JSON, like I’ve shown in my previous answer.

To use the resulting JSON as “real objects” in Bubble, you will have to use another plugin, like JSON Machine, which allow you to “convert” a JSON into Bubble types (for example, you’ll be able to extract a “list of JSON texts” ; and for each of these “JSON texts”, you’ll be able to extract each value

To loop on things, you’ll have to iterate on list of things with “Workflow backend” or some plugins (like “List Shifter KW” or “Orchestra”).

[marius]

Hey y’all,

I’m uploading a CSV file that has a column with JSON data and I have been having a real hard time understanding how to display the this JSON data into a text list.

Here is a sample, the labels will change from row to row.
{"Brand":"Canon","Camera Type":"DSLR","Flash":{"Type":["External Only"],"Maximum Sync Speed":["1/200 Second"],"Flash Compensation":["-3 EV to +3 EV (in 1/3 or 1/2 EV steps)"],"Flash System":["Dedicated: eTTL","Connection: Hot Shoe/PC Terminal"]},"Imaging":{"Maximum Resolution":["Effective Pixels: 30.4 MP","3 × 2: 6720 × 4480"],"Sensor":["Type: CMOS 35mm","Size: 36 mm x 24 mm"],"Crop Factor":["x1.0"],"Bit Depth":["Photo: 14-bit","HD Video: 4:2:0 8-bit","4K Video: 4:2:2 8-bit"],"ISO":["Dynamic Range: Approximately 13.6 Stops","Default Sensitivity Range: 100-32000","Expanded Sensitivity Range: 50-102400","Native ISO: 100"]},"Inputs/Outputs":{"Audio Inputs":["Mic: 3.5 mm (⅛”) stereo mini-jack"],"Audio Output":["Headphones: 3.5 mm (⅛”) stereo mini-jack"],"Video Outputs":["HDMI Type C (mini-HDMI)"],"Additional Inputs/Outputs":["Remote: Canon N3-Type"]},"Item Type":"Camera","Monitoring":{"Viewfinder":["Type: Pentaprism","100% Coverage"],"LCD Panel":["3.2” Rear LCD","Fixed Touchscreen"],"Internal Microphone":["Mono"]},"Mount":"Canon EF","Physical":{"Measurements":["5.9 × 4.6 × 3.0″/150.7 × 116.4 × 75.9 mm","1.76 lbs./800 g"],"Accessory Mounts:":["Tripod:1x ¼-20","Additional: Cold shoe"]},"Power Supply":{"Battery Grip":["BG-E20"],"Battery":["Type: Canon LP-E6N","Approximate Operating Time: up to 900 frames/100 minutes of video","Approximate Recharge Time: 2.5 hours (with included Canon LC-E6 Charger)"]},"Recording":{"Media":["Type: 1x CF/1x SD"],"High Resolution Class Requirement":["Compact Flash (CF): UDMA 7 100 MB/s or faster","SD: UHS-I 90MB/s or faster","UHS-II not supported"],"Formats":["Photo: JPEG, RAW","Video: MJPEG (4K), MPEG-4 AVC/H.264","Audio: AAC, Linear PCM (Stereo)","File: MOV, MP4"],"Modes":["500 Mbps: 4096×2160 (23.98p/24p/25p/29.97p)","180 Mbps (ALL-I): 1920×1080 (50p/59.94p)","90 Mbps (ALL-I): 1920×1080 (23.98p/24p/29.97p)","60 Mbps (IPB): 1920×1080 (50p/59.94p)","30 Mbps (IPB): 1920×1080 (23.98p/24p/25p/29.97p)"],"Special Recording Modes":["High Frame Rate 160 Mbps (ALL-I): 1920×1080 (100p/119.9p)"],"Approximate Record Times":["500 Mbps: 0.25 Min/GB","180 Mbps: 0.62 Min/GB","160 Mbps (HFR): 0.75 Min/GB","90 Mbps: 1.37 Min/GB","60 Mbps: 2.12 Min/GB","30 Mbps: 4.12 Min/GB"],"Maximum Clip Length":["29min 59sec"],"Multi-Slot Recording Methods":["Standard","Auto Switch Card","Rec. Separately","Rec. to Multiple"]},"Sensor Size":"Full Frame"}

If anyone can record a quick video showing me how to do this, I’d love to buy you several coffees

[ts11]

Update: It seems I overlooked your comment above. I’ve purchased the JSON Machine Pro plugin. However, I am having some trouble with the array features. (See below.) If you could offer any help, I’d be grateful.

[New Plugin] JSON Machine Pro

Update: I am now having some issues with the plugin not parsing JSON properly at runtime (or maybe I’m just mucking it up). Recorded another quick video in the hope that you or someone else here on the forum might be able to steer me in the right direction. Thanks! Original Post: Thank you for the quick reply, Jon. I am starting to learn my way around the plugin, but still struggling. It seemed to me that the demo is not functioning very well and gets stuck while executing some of…

Original Post:
Hi there. Thanks for this contribution to the community. I think it will be very helpful. Quick, ignorant question: I am trying to use this plugin to parse an array from a JSON blob and then set that list as the source for a custom state (of type “list of texts”). That said, it seems the parsed JSON is not being recognized as a list, but rather a simple text. How would I go about fixing this?

[wrightj2]

@florian.briand
I am working with an API that returns the following JSON:

{
  "rows": [
    {
      "keys": [
        "search term 1",
        "https://example.com/article-about-keyword-1/"
      ],
      "clicks": 24,
      "impressions": 54,
      "ctr": 0.4444444444444444,
      "position": 2.037037037037037
    },
    {
      "keys": [
        "search term 2",
        "https://example.com/article-about-keyword-2/"
      ],
      "clicks": 17,
      "impressions": 107,
      "ctr": 0.1588785046728972,
      "position": 2.663551401869159
    }
],
  "responseAggregationType": "byPage"
}

And I’m trying to use JSONata/your plugin to change it to something more like this:

 {
  "rows": [
    {
      "keyword": search term 1,
      "URL": https://example.com/article-about-keyword-1/,
      "clicks": 24,
      "impressions": 54,
      "ctr": 0.4444444444444444,
      "position": 2.037037037037037
    },
    {
      "keyword": search term 2,
      "URL": https://example.com/article-about-keyword-2/,
      "clicks": 17,
      "impressions": 107,
      "ctr": 0.1588785046728972,
      "position": 2.663551401869159
    }
],
  "responseAggregationType": "byPage"
}

Basically, I’m trying the break the ‘keys’ part out into ‘Keyword’ and ‘URL’.

Have been playing around for a while in https://try.jsonata.org/ but I’m not getting very far. Any help appreciated.

[melissa7]

This is really cool! I had never heard of JSONata before.

I’ve been playing around with the syntax but having trouble figuring out how to group by a category & sum the groups, then divide by the group sum.

I’m able to group / aggregate (I found this example below on stackoverflow)

But then, I want to divide each item by its group’s sum.

{ “payload”: [
{
“id”: “Irr-1”,
“name”: “Irrigation”,
“timedif_s”: 2
},
{
“id”: “Irr-2”,
“name”: “Irrigation”,
“timedif_s”: 3
},
{
“id”: “Lig-2”,
“name”: “Lights”,
“timedif_s”: 4
},
{
“id”: “Irr-1”,
“name”: “Irrigation”,
“timedif_s”: 1
}]}

The results I am trying to get:

{ “payload”: [
{
“id”: “Irr-1”,
“name”: “Irrigation”,
“timedif_s”: 2
“sum_Irrigation”: 6 [this equals 2 + 3 + 1, the sum of the “Irrigation” categories]
“percent-of-group”: 0.333333 [this = 2 / 6 ]
},
{
“id”: “Irr-2”,
“name”: “Irrigation”,
“timedif_s”: 3
“sum_Irrigation”: 6 [this equals 2 + 3 + 1, the sum of the “Irrigation” categories]
“percent-of-group”: 0.5 [this = 3 / 6 ]
},
{
“id”: “Lig-2”,
“name”: “Lights”,
“timedif_s”: 4
“sum_Lights”: 4 [this equals 4, the sum of the “Lights” categories]
“percent-of-group”: 1 [this = 4 / 4 ]
},
{
“id”: “Irr-1”,
“name”: “Irrigation”,
“timedif_s”: 1
“sum_Irrigation”: 6 [this equals 2 + 3 + 1, the sum of the “Irrigation” categories]
“percent-of-group”: 0.6667 [this = 1 / 6 ]
}]}

The JSONata for the grouping / sum actions:

payload{
  name: $sum(timedif_s)  /* group by name */
} ~> $each(function($v, $k) {
 {"name": $k, "sumtimedif": $v}  /* transform each key/value pair */
})

But how do I divide each group’s “timedif_s” by its group sum?

Here is the JSON Exerciser:
http://try.jsonata.org/ByxzyQ0x4