to JSON or not to JSON?

[springer]

OK, all. I have mentioned that in the classroom my students see lots of TiddlyWiki; I freely navigate links, on the big screen, to pull up relevant bits during discussion. 

But I'm also a kind of database nerd. In my office I work out of FileMaker. FileMaker is the "back end" of what I do in TW (and elsewhere), for lots of reasons.

In TW Classic, I used calculation functions in the database to "extrude" marked-up content to paste in TiddlyWiki. 

• Example 1: my database has hundreds of quiz question-answer sets accumulated over the years, and in TWC I used a calculation to "dress them up" with Eric's NestedSliders syntax. Paste the complex result in a tiddler and.. Instant fun quiz GUI! 
• Example 2: my database has thousands of quoted excerpts from books and articles. I used a database calculation to build a nice slider around each quote (page number and teaser, plus details-style slider to show full quote). After using the find function in the database to bring up a particular subset of quotes, I could grab a tiddler-worth of neatly-formatted excerpts ready to paste and go.

Now, I face a decision: Do I (A) just rework the TW5 calc field in my database (updating so as to dress each quote/quiz element in TW5-specific reveal/details/slider macro syntax, options still under evaluation), or (B) do I figure out how to go all-in on data structure, and use TW5 data features to grab the bits I need from a massive "in-house" JSON tiddler (not thousands of quotes, but hundreds), and use templates to display aspects of the database as desired? The second *sounds* great...

HOWEVER (!), I have no experience with manipulating JSON data yet, and grasp only the syntactic basics of how fields and values are paired (plus the fact that FileMaker does have some JSON-handling functions, so export should be possible). With JSON, there would be a learning curve, but I don't know how steep. (I tried mocking up some JSON-looking stuff and pasting it into TiddlyWiki and giving it JSON data "type" and my wiki just blinked back at me and said, OK, there's a buncha funny looking text...)

If I understand correctly, it seems the advantage of the JSON approach (once I figure out how to import the data) is that I'd have great flexibility to re-filter things on the fly within TW, and *also* great flexibilty in GUI. So, if I suddenly discover some new display macro approach (in the reveal/details/accordion/slider world) or I realize I want to change which fields to display and how, I modify my template once, and all the tiddlers that rely on it are instantly updated. On my old system, when I became inspired to tweak how these things display, I would have to shuttle back and forth to my FileMaker database, and perform copy-paste operations for each tiddler with the new syntax. 

So, for those fluent in JSON (and yet not unsympathetic to JSON newbies), would you advise me toward (A), or toward (B)? Or, am I not grasping the choice well yet?

By the way, I'm guessing that the path of importing JSON data, if I don't ever convert it into regular tiddlers, seems to place more importance on the possibility of freelinking, since my database of quoted passages uses many terms that are in my glossary, and I've love for them to link, but all the data will stay "under the hood" within the JSON tiddler, right?

[Joshua Fontany]

You have a good understanding of the different approaches. One thing to note is that native-Tiddlywiki syntax can only understand "flat" (1 level deep) JSON structure.

Being able to proccess large-data-sets from other data-management structures i one of the reasons I made the "JsonMangler" plugin. See demo here:

https://joshuafontany.github.io/TW5-JsonMangler/

With this plugin installed, you can "dig down" into nested or mutli-level JSON structures with Tiddlywiki widgets/syntax.

For example, to transclude the "videoId" of the second item (index 1) in one of my Test json tiddlers (https://joshuafontany.github.io/TW5-JsonMangler/#Test%2FYouTubeAPI.json), which is full of nested objects representing YouTube search results, you could write:

{{Test/YouTubeAPI.json##\items\1\etag\id\videoId}}

In vanilla Tiddlywiki, you would only be able to access the "items" object and would need to manually split the items into individual tiddlers.

With the ability to access nested data, the rest of the Tiddlywiki toolkit (List Widget, Filters, etc) will allow you format or even break up the data into individual tiddler.

Let me know if the docs at the example wiki are in need of updating. :)

Best,
Joshua Fontany 

[springer]

Hi Joshua, thanks for your reply. The JsonMangler sounds impressive!

I suspect that (at least for now) flat table (vanilla) functionality is fine, since I still do all my relational database work in FileMaker.

What I would really benefit from is some kind of walkthrough of a very basic JSON import process (starting not from a "file" per se but from a big hunk of exported text, which is what my database will yield), along with a couple simple demos of ways to use TW macros or templates to filter and extract field data from it. From there I would begin experimenting to produce the templates I need for things like slider sets for batches of records from the JSON data. 

One other thing that a JSON-savvy person might be able to clarify, quite quickly, is how much of a nuisance it will be to handle things like quotation marks or style formatting within my original data. I assume JSON files have some kind of escape sequences available, and that these play nice with TW5, but a confirmation would make a big difference. 

I'll poke around at your site to learn a bit more, but I admit that even the simple transclusion task you described wasn't coming through with anything for me (I created a new tiddler at your site and pasted in the string from your post, but the resulting tiddler showed nothing). Perhaps I just need pointers to get oriented on the basics, since your wiki seems to be doing stuff at a much higher level than the simple task (retrieve-filtered-records-from-flat-JSON-and-display-selected-fields-according-to-template) that I'm aiming for.

Many thanks again!

[Joshua Fontany]

Yes, currently that is a non-trivial task, as there really is no "standard Json format", so all importing o far from database-structures has been ad-hoc work (I believe Evan Balster has a good CSV importing workflow).

Also, I apologize for my example wiki-code. It was late and I messed up both the path _and_ my seperator character (should have been 'slash' not 'backslash'). Sheesh, good one Josh (lol).

The working transclusion would be: {{Test/YouTubeAPI.json##/items/1/id/videoId}}

Moving on to your query, would your output data have a standard set of fields, and what kind of json structure would there be? Once we have that nailed down a bit, I can suggest various Filter tricks. 

Best,

Joshua Fontany

[springer]

Joshua, yes trying the new transclusion at your site yields a string ("Eqa2nAAhHN0") -- is that what videold is supposed to look like?

For the main task I'm interested in, there's no structure beyond a flat table with fields (almost uniformly populated) as follows:

qID:

source:

page:

pageMod:

excerpt:

tagline:

session:

The way the data gets used in my TW5 instance is that I'd filter by "session" (24 sessions) and get all the associated excerpts displayed inside sliders (or using details widget or some such) so that one line with key info, per excerpt, is always visible (as "summary"), but the bulk of each paragraph-worth of text is hidden until needed.

The original database's excerpt field does have all sorts of troublesome stuff (quotes, brackets, occasional paragraph breaks), and the source field may have quote marks, colons, etc. as well. Even if I deploy a substitution trick for the paragraph breaks, it's definitely too complex to work easily as CSV, given the gazillions of commas and quote marks. 

Given this complexity, does it sound like I should just stick to the low road and do the dressing-up within my database prior to a copy-paste process? (It's only 24 copy-paste operations to get the sets I need. I'd lose the flexbility to adjust details on the fly, but that's of marginal value if converting into JSON, plus reconstructing punctuation etc as TW5 peeks into the JSON, turns out to be an ordeal.) 

-Springer

[Joshua Fontany]

I have been giving this some thought, but need to know the exact structure of the Json output file you would be importing into tiddlywiki. Is it similar to the below?

[

fieldNames,

row1,

row2,

row3

]

If so we can template tiddler-field-names from row0, and then split each row to it's own tiddler. From tehre the native Filter, List, etc widgets will build your UI.

The problem here is if each row is one string (wrapped in it's own quotes), or a number of strings (each wrapped in quotes, at which point this is not a "flat" 1 level deep JSON). OR is it coming out of the DB in some other format?

The other problem being mentioned upthread: Embedded quotation marks (etc) in the data. The best thing to do would be to export all fields from the DB where this occurs as "HTML encoded text". Then you can use the `decodehtml` filter operator on each of those text blocks before displaying it. See: https://tiddlywiki.com/#decodehtml%20Operator

Best,
Joshua F

[TonyM]

Springer,

My own approach to this kind of issue would be to build a JSON using the multiple tiddler format you can see inside the $:/import temp tiddler, or plugins, this is a very simple json format and does not use complex json syntax. Each of these with be changed into a tiddler on import. 

Treat the different items either as fields; 

qID:

source:

page:

pageMod:

excerpt:

tagline:

session:

Or make each tiddler a dictionary tiddler which you can overlay with a view template. In which you would place all these "fields" in the text.

I am sure there is some devil in the details but I am confident they can be addressed.

Regards

Tony

[springer]

Tony and Joshua,

I had been leaning toward the simpler JSON format that Tony is describing. But both of you are suggesting an approach that ends up with hundreds of tiddlers being generated from my database file during the import process... 

Well, I admit that a big part of the attraction of the JSON idea, for me, was the hope that this trove of data (24 x ~10-15 paragraph-long items plus their associated record data) would be all tucked into one JSON tiddler, portions of which would get "served up" in slices as needed. (That would have kept my wiki from feeling littered with so many tiddlers that I need to extract data from, but not edit, and which don't ever need to be visible as individual tiddlers to students.)

At this point (with substantive content-edits to complete in the next week, a few other TW5 upgrade glitches to fix, and little longterm payoff for embarking on the JSON learning curve) I think I should take "the low road" with this problem. That means settling on the DetailsWidget accordion-style markup I'll use, and getting my database to organize the 24 excerpt sets I expect to use, and bundle them up with the right syntax. Then I can drag each set-worth into the wiki and I'm good to go (just as it used to be with Eric's NestedSliders). I'll be locked into DetailsWidget syntax for now (rather than slider or reveal or appear), but of course the css will still be flexible.

In case anyone's curious, I did a pretty thorough comparison of the slider options I could find for TW5, and DetailsWidget GUI won out because expansion gets triggered anywhere on the "summary" block, rather than needing the eye-hand coordination to target a little toggle-triangle at left (or some other small button) to do the work -- again, it's something that matters while multi-tasking with a classroom-projected screen, though probably not while working in ordinary desktop environments. (I'm still sad that DetailsWidget doesn't allow me to apply styles to the text within the summary/label area, though; for academic purposes, it's really best to be able to italicize titles and foreign-language terms, etc., wherever they appear.)

Though I'm letting go of this JSON curiosity for now, I hope the discussion might prove useful to someone down the road, and I'm grateful for your input!

-Springer

[TonyM]

Springer,

If you want to bundle tiddlers in a JSON, there is a very simple way to do it. If you have you tiddler json file drop it on a wiki, normally you would click import and they would become individual tiddlers, however rather than import do the following.

• Rename the $:/Import tiddler to you bundle name
• Change the plugin type field on your bundle tiddler to plugin and delete the status field
• Save and reload your wiki. 

The tiddlers will be stored as shadow tiddlers and if edited an overwritten shadow tiddler. Your could delete the plugin at any time, bhut remember to clean up any edited tiddlers. They will thus stay as a single json file but be accessible as tiddlers.

I have argued before that we introduce a "data" plugin type for this purpose.

Regards

Tony

[TonyM]

SPringer,

I forgot to say don't worry about having hundreds of tiddlers. A tiddler is a data record by another name. From memory and a little tweaking there are 66,000 plus tiddler single file wikis that work fine.

Regards

Tony

[Joshua Fontany]

I'm experimenting with exporting a product database to then import into TiddlyWiki to display/search/etc.

So, one of the big questions is updating. If each "snapshot" of the database is one giant Json Tiddler, then this makes it much easier to point the UI to an updated Json Tiddler "snapshot" with new inventory quantities, prices, new product, products removed or archived, etc.

If I have to split it up into individual tiddlers each time, then we need logic to update/sync/delete to match the new incoming Json file.

It's an interesting problem. I think I'm going to add a few functions to my JsonMangler plugin to help process CVSs (there are some good libraries out there that convert back and forth between CSV and Json Arrays) and maybe a bit on the "splitting a Json Array into individual tiddlers" side of things. That, of course, runs into the "what characters are legal in tiddler field-names" issue that was raised recently - as Json is very flexible with key-names...

Best,
Joshua F

[TonyM]

Joshua,

See this thread in dev https://groups.google.com/forum/?hl=en#!topic/tiddlywikidev/daX5Z5RCAfM

Importing a JSON tiddler containing tiddler format data as a plugin has some advantages. Perhaps even for other JSON files. Perhaps you could consider my idea of data plugin-type I raise in the thread. 

It stands to simplify the logic needed with update/sync/delete to match the new incoming Json file and even present additional features. 

One advantage being is you can load a considerable set of tiddlers and have modifications become active while preserving the original import. This allows detection of changes, undo etc... In someways one could consider a plugin snapshot of data as a "Stocktake", with the ability to apply subsequent transactions. Some smart workflows would export a new snapshot as needed and repackage it as a new point in time data set. Arguably by retaining all snapshots you could do a full long term data analysis (perhaps in a seperate wiki).

There are also possibilities of transferring only changes thus reducing the size of imports.

Less discuss further if it interests you.

Regards

Tony