Arrangement Order and Multiple Columns Sorting Order of Associations with Missing Keys

Question

I have the following well-formed JSON string

jsonResult="{\"result\":[{\"@type\":\"d\",\"@rid\":\"#12:0\",\"@version\":1,\"@class\":\"PERSON\",\"perName\":\"Jay\",\"perSurname\":\"Miner\"},{\"@type\":\"d\",\"@rid\":\"#12:1\",\"@version\":4,\"@class\":\"PERSON\",\"perName\":\"Luca\",\"perPhones\":{\"office\":\" 3906222222\",\"mobile\":\" 393333888\"},\"perAge\":34,\"perCity\":\"Rome\"}]}"

that I convert to a list of rules

jsonRules = "result" /. ImportString[jsonResult, "JSON"]

I would like to apply two generic type of functions/operators. The first takes an ordered list of keys for the desired rearrangement and the second a list of ordering functions for the type of sorting each column will have. You can combine them of course but for clarity and learning purposes I would prefer to see them separately.

Something like....

rulesRearrange[
    {"perSurname","perName","perAge","perPhones","perCity","@rid","@version","@type","@class"},
    jsonRules]

and

rulesSortOrder[.....,jsonRules]

that will produce the following output

jsonRulesSorted = {{"perSurname" -> "Miner", "perName" -> "Jay", 
"perAge" -> Missing[], "perPhones" -> Missing[], 
"perCity" -> Missing[], "@rid" -> "#12:0", "@version" -> 1, 
"@type" -> "d", "@class" -> "PERSON"},{"perSurname" -> Missing[], "perName" -> "Luca", "perAge" -> 34,"perPhones" -> {"office" -> " 3906222222", 
  "mobile" -> " 393333888"}, "perCity" -> "Rome", 
"@rid" -> "#12:1", "@version" -> 4, "@type" -> "d", 
"@class" -> "PERSON"}};

that can be nicely presented and formatted with associations and Dataset

Dataset[ToAssociations[jsonRulesSorted]]

I am adding the following dataset for the sorting part of my question. I would like to rearrange it with the order of columns as {"a", "@x", "@y", "@z", "b", "c", "d", "e"}

lr = {{"a" -> 2, "@y" -> "PERSON", "@x" -> "#12:1", "c" -> 8, 
"@z" -> 4}, {"d" -> 5, "@x" -> "#12:5", "b" -> 4, "e" -> 9, "@y" -> "COMPANY","@z" -> 10},{"@x" -> "#12:8", "@y" -> "PERSON", "@z" -> 12, "a" -> 1, "b" -> 2,"c" -> 3, "e" -> 4},{"@x" -> "#12:8", "@y" -> "PERSON", "@z" -> 10, "a" -> 2, "b" -> 7,"e" -> 4}};

Then I would like to see this sorted by specifying the sorting order in the first two columns as Ascending and Descending respectively. This is similar to an excel column sorting feature.

Answer 1

Let me start by generating a Dataset from your JSON string. Note that I adjusted your expressions a little.

jsonRules = "result" /. ImportString[jsonResult, "JSON"];
originalds = Dataset[Association @@@ jsonRules]

Rearranging the columns can be accomplished without a helper function. Instead, we pick columns in originalds to generate a new data set with the columns rearranged:

rearrangedcolumnds = originalds[All, {"perSurname", "perName", "perAge", "perPhones", 
                                      "perCity", "@rid", "@version", "@type", "@class"}]

---

The second part of your question did not make a lot of sense to me as it is posed right now: if I sort any one column of a dataset, I would like all other columns to "follow suit" without breaking the connections within the data. For instance, imagine imposing a sort that swaps the entries in the first column; I would want entries in all other columns to swap with them, or otherwise the data would become jumbled!

For instance, suppose that you want to sort the dataset by age:

rearrangedcolumnds[SortBy["perAge"]]

The result correctly swaps the whole second row with the first one, so that data pertaining to Luca are still kept together.

Perhaps I am misunderstanding your question though, in which case you might want to clarify it or otherwise comment.