JSONMangler and nested JSON

[Werner]

Dear all, heavy user and rare poster here. Using TW has become a daily treat for me, but I have managed to still stay at the surface so far. Now, I have to dive in deeper.

I have been using TW so far to store semi-structured information, but now I need to integrate structured information, i.e. a database featuring an 1:n relation. 

I thought it might be a good idea using JSON. I also thought, for my purposes, I don't want to mimic an RDBMS in TW with all the primary key hassles and a deluge of .TID files (I am running TW on Node.js), so I wanted to use a JSON tiddler for the entities and use arrays in JSON for sub-entities. 

I came across Joshua Fontenay's JSONMangler plugin (Kudos, Josh!) and so far so good.

BUT

Eventually I want to implement CRUD (create-retrieve-update-delete) functionalities for this special data. And I want to display the 1:n relation in a single tiddler. And this is where I'm stuck at the moment:

- how to get the total number of array items in a JSON array in order to be able to display the 1:n 

apart from the variable array length, the JSON structure is fix, so everything else should be fairly straightforward. OK, there are some numeric fields in the array I need to sum up, but one thing after the other...

Any insight would be greatly appreciated

Best,

Werner

[TW Tones]

Werner,

Despite clearly having a lot in common with an understanding of databases I don't completely follow you need or argument here. However I believe I can offer you some helpful leads.

One part that confuses me is your 1:n or one to many, I have already build one to one, one to many and many to many half a dozen ways without a data tiddler in tiddlywiki. What is the particular issue here?

First look at tiddlywikis existing functionality.

• The history mechanism stores tiddler titles and more during a session in the $:/historyList this may be an analogue
• The Import and export mechanism extract and package tiddlers as json representations
• Mohammad's Trash Plugin moves deleted tiddlers into another structure to keep them aside, I think it is JSON as well.
• In my demo site here the active tiddler process extracts titles from the history list using splitregexp not standard JSON as the key is not unique

However It would not necessarily be too concerned about using tiddlers unless your number will be truly astounding in numbers. We had a 66,000 word tiddler example in the last year and that was single file.

Speculation

If you packed more static tiddlers (eg a reference table)  into a plugin and access them as shadow tiddlers you are effectively packing them into a json file and allow yourself to edit exceptions and the database becomes the combination of the packaged and overwritten providing a lot of flexibility to your design. In effect you CRUD will make use of standard tiddlywiki features, just treat the records like standard tiddlers. The D or delete of CRUD could get smart (adding and removing from JSON) or you could simply flag deleted tiddlers as such and treat them as deleted with an occasional rebuild recommended after N tiddlers are flagged as deleted.

I can already foresee a lot of sophisticated features being possible including tracing and logs, difference engines, database snapshot at a point in time and more. I have recently designed an alternative to shadow tiddlers I call ghost tiddlers that may also be a useful algorithm.

Offer

I as a tiddlywiki superuser, I am investing totally in the TiddlyWiki platform, if you are interested in collaboration to build a database model process, making full use tiddlywiki's unique features I think it will be a good investment in the future.

Lets start the conversation.

Regards

Tony

[Werner]

Hi Tony,

thanks for taking your time and your extensive reply. Perhaps I've made it too complicated. Ok, I try to reformulate it. Disclaimer also: I work in a corporate environment, so I'm not completely free in the tools I am allowed to use (to my surprise, Node.js is ok - yessss!) and how much information I can disclose.

Let's put it that way: I have set up an internal information repository which contains more or less textual information only so far. But now and then, I would like to display some figures. And I would have to perform some simple arithmetics with that figures (basically summing up).

Let's call the things I am looking at "facilities". A "facility" should be displayed in a templated tiddler, displaying textual and quantitative information. Each "facility" can feature several "units" (that's the 1:n relation), figures for which should also be displayed and/or shown as a grand total. 

The data for the facility/unit information should initially come from an Excel table. For further use (the CRUD part), I would like to create a TW-based interface. As "facility" is a comprehensive entity for my purposes, I thought it would be ok to store it in a nested JSON tiddler, i.e. store the units per facility in a JSON array. This, of course, breaks the RDBMS dogma, but it saves me from having to fiddle with primary keys. Mimicking a RDBMS in TW is definitely not a rabbit hole I want to jump into. If I needed something like that, I would try if I could connect a DB backend via Node.js (in my special environment I would end up with SQLite or ODBC), and let this do the grunt work. 

All this being said, here's the core of my question: Accessing nested JSON data using Joshua Fontenay's JSONMANGLER plugin works great, as long as the index position is known, but I need a way to determine the length of a variable-length array in a JSON structure, for not running out-of-bounds while looping into the nested data. I understand $:/History is such a variable-length array, and thanks for pointing me at this. In that case, maybe I need to look up how data are retrieved from there. I know, as a workaround, I still could store the number of units as a data field in the JSON structure, but then I would have to take care of it programatically, which I want to avoid.

Thanks again and apologies for any confusion I caused

Werner

[Joshua Fontany]

Hi,

Great to see people experimenting with my tools. 

Two things to note. 1) 0-index based arrays can always have a new object written to an index equal to the length of the array. Example, "[0,1,2]", I can push "3" to {{MyJson##3}}, and it will be "[0,1,2,3]". Neat trick. 2) You can count an array's length using filters as long as you know the position of the array within the nexted JSON structure, like so:
```

<$var name="arrLength" filter="[[MyJson]indexes[/path/to/array]count[]]">

Array Length: <<arrLength>>

</$var>  

```

Combine the two techniques like so (note the final slash on the new path gets added on as a prefix before adding the path to the array):

```

<$var name="arrLength" filter="[[MyJson]indexes[/path/to/array]count[]]">

<$var name="newIndex" filter="[<arrLength>addprefix[/]addprefix[/path/to/array]]">  

<$button setTitle="MyJson" setIndex=<<newIndex>> setTo="New Data" />

</$var>

</$var> 

``` 

You could use the form `setTo={{New Data Tiddler}}` to grab the new data from the text-field of the "New Data Tiddler", etc, etc.

Best,
Joshua Fontany

[Werner]

Hi Joshua,

thanks so much for your answer. This has taken me a lot further.

But, reconsidering my original question, I found, that, maybe, I got stuck too much in the procedural paradigm, and a more elegant approach would be, if one could could use each[] for looping through a JSON array. Taking your example this should look something like:

<$list filter="[[MyJson]indexes[/path/to/array]each[]]">

{{##FieldFromJsonArray}}

</$list>

I tried to do it, but didn't succeed. I understand that each[] expects a list separated with spaces, so my assumption would be that this does not work unless an overloaded version of each[] is provided. OTOH, I found an undocumented function ForEach[] in JSONMANGLER. Any clue?

I created three JSON test tiddlers. One with a nested array of 4 elements and another with 3 and a third one with a one-element array. I looped through them like this

<$list filter="[tag[Facility]]">

...

<$set name="arrLength" filter="[indexes[/Actions]count[]]">

Array Length: <<arrLength>>

</$set> 

</$list>

and was able to display all fields and the array fields as JSON text. Strangely, the arrLength variable displayed a value of 4 for all three tiddlers. No idea why. Will have to dive deeper into it.

Thanks again

Werner

[Joshua Fontany]

I think you are almost there. One thing to keep in mind in Tiddlkywiki is the difference between "shorthand" Wikitext and "long form" Widgets.

{{Title##Index}} is "shorthand" Wikitest that is then rendered into the following by the TW core:
```

<$tiddler tiddler="Title">

  <$transclude tiddler=<<CurrentTiddler>> index="Index" />

</$tiddler>

```

As you can see, leaving out the first part, like so {{##Index}} would retain the value of the "current" <<currentTiddler>> variable and just use the <$transclude> widget to pull an Index. Similarly, if you leave out the "variable" parameter of a $list widget, it assumes you want to set the <<currentTiddler>> variable in each rendered tempalte. This is equivalent to your "each()" request. (Make sure to have an empty line after the opening <$list> tag if you want block fomatting.)

The next thing to note is that with JsonMangler you always want to use full paths to any nested data. Always consider the Tiddler as the object, and path from the root to the required data. Your list would look like so:
```

<$list filter="[tag[Facility]]">

<$set name="arrLength" filter="[<currentTiddler>indexes[/Actions]count[]]">

Facility Array Length: <<arrLength>>

<$list variable="arrItemPath" filter="[<currentTiddler>indexes[/Actions]addprefix[/Actions]]" >

<<arrItemPath>>:  <$transclude tiddler=<<currentTidler>> index=<<arrItemPAth>> />

</$set> 

</$list>

```

Best,

Joshua Fontany

[Werner]

OK, it took me quite a bit to get my head wrapped around all this, but I've come up with a solution that works for me. In order to dive into the nested array and access the indexes in there, I had to resort to a macro renderIndex(), but, as a positive side effect, this makes the code more readable. So here's the example code. Feel free to comment if I unnecessarily complicated it. Also, I still need to come up with a solution to add up fields with numeric content in a JSON array. Input here would be greatly appreciated

<!-- demo code -->

\define renderIndex(path: "0" index: "") <!-- renders indexes in JSON array -->

<$set name="myFld2" tiddler="$(currentTiddler)$" index="$path$/$index$" > 

Displaying $path$/$index$: <<myFld2>><br>

</$set>

\end

<$list filter="[tag[Facility]]">

<$view field="title"/><br>

Actions array Length:<br>

<$set name="arrLength" filter="[<currentTiddler>indexes[/Actions]count[]]">

Array Length: <<arrLength>>

</$set>  

Actions array Full Content as JSON string:<br>

<$set name="myVar" tiddler=<<currentTiddler>> index="/Actions">

<<myVar>>

</$set>

Looping through Actions array:<br/>

<$list variable="arrayIndex" filter="[<currentTiddler>indexes[/Actions]]">

<$set name="myFld", filter="[<currentTiddler>indexes[/Actions]addprefix[/Actions/]]" select=<<arrayIndex>> > 

Displaying contents of <<myFld>> as JSON string:<br>

<$transclude tiddler=<<currentTiddler>> index=<<myFld>> /><br>

<!-- Accessing fields in nested array -->

<$macrocall $name="renderIndex" path=<<myFld>> index="Action Date" />

<$macrocall $name="renderIndex" path=<<myFld>> index="Action Type" />

</$set>

</$list>

</$list>

<!-- demo code ends here -->

So, Joshua and Tony, thanks so much for helping me on the way. Was quite a bit of a learning curve, but I enjoyed it.

Best, Werner