sorting json data

[Jean-Pierre Rivière]

I have a structured json data tiddler name "projects", which looks like this:

[ 

  { "project": "pleiade", "name": "Pléiade v5", "url": "http:pleiade.test.acmeserver.com" },

  { "project": "rnvp", "name": "RNVP preprod", "url": "https://rnvp.preprod.acmeserver.com" },

  { "project": "ars", "name": "application de réservation de salles", "url": "http://ars.test.acmeserver.com" }

 ]

I want to show this a list sorted on the "name" field. This is the sorting part which I struggle to get, unsuccessfully.

I have installed the jsonmangler plugin. I can't find a reherence doc though. With numerous tries, I've been able to get my list, but not sorted.

I have this idea to constitute a list of tiddlers where each item is "name^index" where name is the name and index the index of each record. Then I wold sort the list and reduce it to the index part to get the sorted list of index which would enable me to achieve the display I want.

But I cannot build this name-index list. I have the list of the names in the natural order and the list of numbers which are the natural indexes of the items. I can then get a list list like "t0 t1 t2 0 1 2". I can in fact be a little clever and build "t0 t1 t2 ^0; ^1; ^2;"  instead and then what I need is getting "t0 ^0; t1 ^1; t2 ^2;". This is the critical step I don't know how to do.

(because from there I would join it in "t0^0;t1^1;t2^2;", split that along the ";" character and get the list of name^index I wish.)

I know how to do it manually in the case of three records, like this:

<ol>

<$list variable="demo" filter="[enlist[t0 t1 t2]append[^0; ^1; ^2;]move:-2[^0;]move:-1[^1;]join[]split[;]!is[blank]]">

<li><<demo>></li>

</$list>

</ol>

So the general idea would be to generate the part like "move:-2[^0;]move:-1[^1;]" from the number of records. That is beginning to be very complex for what that is, how common this seems to be to me. So surely there is a nicer way to do it.

thanks in advance,

[Mark S.]

This is a slightly different approach that I've used before. The names are all fetched in a form where they can be wikified, turned back into a list, and then sorted. Then a lookup is done by name for each item and the project is extracted. I think from this you can see how you would proceed with extracting the URL as well. For this to work of course, the names have to be unique. I called the data tiddler "data" here.

\define getnames()  <$vars lb=" [[" rb="]]" sep="$$"><$list filter="[[data]indexes[]addsuffix[/name]]" variable="fetch">{{{ [[data]getindex<fetch>addprefix<lb>addsuffix<rb>] }}}</$list></$vars>

\define getprojectbyname(name) 

<$list filter="[[data]indexes[]]" variable="index">

<$list filter="[<index>addsuffix[/name]]" variable=namepath>

<$list filter="[[data]getindex<namepath>compare:string:eq<__name__>]">

<$list filter="[<index>addsuffix[/project]]" variable=projectpath>

{{{ [[data]getindex<projectpath>] }}}

</$list></$list></$list></$list>

\end

<$wikify text="""<<getnames>>""" name="names">

<$list filter="[enlist<names>sort[]]" variable="name">

Name: <<name>> <br/>

Project: <$macrocall $name="getprojectbyname" name=<<name>> /> <br/><br/>

</$list>

</$wikify>

I've often thought how handy it would be if you could get two outputs from a list instead of just one. e.g. both and index and the index/name value. 

[Jean-Pierre Rivière]

Thank you Mark. The macro is a nice thing, but it is not a filter. This allow to build the html for a sorted table.

But there might be some case where I would need to refine the filter to get a whole list of more complex data out of my filter. BUT this is not true as of now.

With your help, I can achieve what I want in a reasonable amount of code, and clear code. That's great!

[TW Tones]

In your example there is a field with the value "name" for every entry, they do not need to be sorted. 

If on the other hand you are referring to values in the second field like "pleiade", now that is a different story.

Another approach without the JSON mangler is to parse each line, then parse each value;

Here is a hint at how it can be done. Note no json handling, just text handling.

\define open-curly() {

\define open-square() [

\define close-square() ]

\define dbl-quote() "

\define comma() ,

\define colon() :

<$list filter="[{Data}splitregexp[\n]!prefix<open-square>!prefix<close-square>]" variable=each-line>

;<<each-line>><br>

<$list filter="[<each-line>split<dbl-quote>nth[4]]">

</$list>

</$list>

Regards

Tones

[Jean-Pierre Rivière]

nice one too.

I had not thought about using macros to define constants. Handier that using variables. Is that a regular feature of your coding, Tones?

[Jean-Pierre Rivière]

Thanks to the ideas of Tones, I have found out how to solve elegantly my problem. And found out two oddities as well. But firs, let 's see the the data in tiddler "projets-json":

[

  {

       "project": "pleiade",

       "name": "Pléiade v5",

       "url": "http:pleiade.test.acmeserver.com"

   },

   {

       "project": "rnvp",

       "name": "RNVP preprod",

       "url": "https://rnvp.preprod.acmeserver.com"

   },

   {

       "project": "resa",

       "name": "application de réservation de salles",

       "url": "http://ars.test.acmeserver.com"

   }

]

Notice that the relative order of the "project" and "name" index is not always the same. on purpose.

And here comes the code!

=========================

\define exp-med() \s*},\s*{

\define exp-beg() [[]\s*{\s*

\define exp-end() \s*}\s*]\s*

\define exp-grab() .*"name":\s"([^"]*)"[\s\S]*

\define exp-build() AKA_$1_YET

\define exp-grab01() [\s\S]*"project":\s"([^"]*)"[\s\S]*"name":\s"([^"]*)"[\s\S]*

\define exp-build01() $2^$1

\define exp-grab02() [\s\S]*"name":\s"([^"]*)"[\s\S]*"project":\s"([^"]*)"[\s\S]*

\define exp-build02() $1^$2

\define exp-final() [^^]*\^

!! step 1

Get the whole json in a single line and from there into lines of exactly one complete json object each.

<$list filter="[{projets-json}join[]splitregexp<exp-med>splitregexp<exp-beg>splitregexp<exp-end>!is[blank]]" variable=each-line>

«<<each-line>>»<br>

</$list>

---

!! experimental step 2

add an experiment for capturing a desired kind of data. Normally, only the name would be on display, between AKA_ and _YET.

<$list filter="[{projets-json}join[]splitregexp<exp-med>splitregexp<exp-beg>splitregexp<exp-end>!is[blank]search-replace::regexp<exp-grab>,<exp-build>]" variable=each-line>

«<<each-line>>»<br>

</$list>

---

!! real step 2

add fetching "name" and "project" values and arrange them into NAME^PROJECT.

<$list filter="[{projets-json}join[]splitregexp<exp-med>splitregexp<exp-beg>splitregexp<exp-end>!is[blank]search-replace::regexp<exp-grab01>,<exp-build01>]" variable=each-line>

«<<each-line>>»<br>

</$list>

!! step 3

As we can't be sure of the order of the index within json objects,we have to have two scans: one name then project and the other one as project then name. This method can be extended up to three fields ; beyond that would be a nightmare (can we sort the fields within a json object?).

<$list filter="[{projets-json}join[]splitregexp<exp-med>splitregexp<exp-beg>splitregexp<exp-end>!is[blank]search-replace::regexp<exp-grab01>,<exp-build01>search-replace::regexp<exp-grab02>,<exp-build02>]" variable=each-line>

«<<each-line>>»<br>

</$list>

!! step 4

Sort the lines

<$list filter="[{projets-json}join[]splitregexp<exp-med>splitregexp<exp-beg>splitregexp<exp-end>!is[blank]search-replace::regexp<exp-grab01>,<exp-build01>search-replace::regexp<exp-grab02>,<exp-build02>sortan[]]" variable=each-line>

«<<each-line>>»<br>

</$list>

!! step 5

only display the PROJECT value (the part after after "^").

<$list filter="[{projets-json}join[]splitregexp<exp-med>splitregexp<exp-beg>splitregexp<exp-end>!is[blank]search-replace::regexp<exp-grab01>,<exp-build01>search-replace::regexp<exp-grab02>,<exp-build02>sortan[]search-replace::regexp<exp-final>,[]]" variable=each-line>

«<<each-line>>»<br>

</$list>

=========================

and here is what we get:

======================

step 1

Get the whole json in a single line and from there into lines of exactly one complete json object each.

«"project": "pleiade", "name": "Pléiade v5", "url": "http:pleiade.test.acmeserver.com"»
«"project": "rnvp", "name": "RNVP preprod", "url": "https://rnvp.preprod.acmeserver.com"»
«"name": "application de réservation de salles", "url": "http://ars.test.acmeserver.com", "project": "resa"»

experimental step 2

add an experiment for capturing a desired kind of data. Normally, only the name would be on display, between AKA_ and _YET.

«"project": "pleiade", AKA_Pléiade v5_YET»
«"project": "rnvp", AKA_RNVP preprod_YET»
«AKA_application de réservation de salles_YET»

real step 2

add fetching "name" and "project" values and arrange them into NAME^PROJECT.

«Pléiade v5^pleiade»
«RNVP preprod^rnvp»
«"name": "application de réservation de salles", "url": "http://ars.test.acmeserver.com", "project": "resa"»

step 3

As we can't be sure of the order of the index within json objects,we have to have two scans: one name then project and the other one as project then name. This method can be extended up to three fields ; beyond that would be a nightmare (can we sort the fields within a json object?).

«Pléiade v5^pleiade»
«RNVP preprod^rnvp»
«application de réservation de salles^resa»

step 4

Sort the lines

«application de réservation de salles^resa»
«Pléiade v5^pleiade»
«RNVP preprod^rnvp»

step 5

only display the PROJECT value (the part after after "^").

«resa»
«pleiade»
«rnvp»

=======================

Job done!!!

Now for the two oddities:

1) the syntax search-replace::regexp and not just search-replace:regexp was quite a nasty surprise. It should be noted or given an example in the doc.

2) the dot . in regexp should include space (regular space char, of code 32). it does not. This explains the results in the experimental step 2. The workaround is to replace the dot by [\s\S] which is not helping the readability of the regexes. See https://regular-expressions.mobi/dot.html for more information.