sort json document using jq

Question

I have a json document that is a (SSAS Tabular model .bim file)

Its full structure is attached example json but it is a nested object structure as per below. Every object has a name property that I would like to use to sort on

{
"name": "SemanticModel",
"compatibilityLevel": 1200,
"model": {
"culture": "en-US",
"dataSources": [
  {
    "name": "BlahDW",
    "connectionString": "Provider=SQLOLEDB;Data Source=sql.blah.com;Persist Security Info=false;Integrated Security=SSPI;Initial Catalog=Blah",
    "impersonationMode": "impersonateAccount",
    "account": "blah\\blah",
    "annotations": [
      {
        "name": "ConnectionEditUISource",
        "value": "SqlServer"
      }
    ]
  }
],
"tables": [
  {
    "name": "Employees",
    "isHidden": true,
    "columns": [
      {
        "name": "Employee Key",
        "dataType": "int64",
        "isHidden": true,
        "isUnique": true,
        "isNullable": false,
        "sourceColumn": "Employee Key"
      },
      {
        "name": "Employee Code",
        "dataType": "string",
        "isHidden": true,
        "sourceColumn": "Employee Code"
      },
      {
        "name": "Employee Name",
        "dataType": "string",
        "isHidden": true,
        "sourceColumn": "Employee Name"
      },
      {
        "name": "Home Village Code",
        "dataType": "string",
        "isHidden": true,
        "sourceColumn": "Home Village Code"
      }
    ],
....

I have tried the walk/1 method as mentioned here How can I completely sort arbitrary JSON using jq? but it doesn't work as it is not trying to sort the objects in the collections I don't think.

I have found that this works ".model.tables|=sort_by(.name)" to just sort one collection (thanks to How to sort a json file by keys and values of those keys in jq) but I cannot work out how to combine this with the walk so I don't need to explicitly recreate the same json structure

I am at my limit of my jq knowledge here so wondered if someone could put me in the right direction

score 4 · Accepted Answer · answered Apr 04 '17 at 16:53

4

You can sort all the arrays by "name", while keeping everything else intact, like this:

walk(if type == "array" then sort_by(.name) else . end)

answered Apr 04 '17 at 16:53

zeppelin

8,947
2
24
30

1

Thanks but that doesn't seem to work? See the jqplay with example https://jqplay.org/s/plpHDzbjnW Everything is still in the same order as the source – Brett Apr 05 '17 at 11:37
@Brett, your snippet starts with a comment `#`, so it does not apply at all (as it is just one line) – zeppelin Apr 05 '17 at 13:38
I've just tested it with 'jq 1.5' (command line), and it works just fine for me. – zeppelin Apr 05 '17 at 14:06
Fantastic yes thanks it does work in real life. I just needed to change it to sort_by(.name?) as not all arrays always have a name type. Ideally I would like to reorder the objects inside the array but not change the order of the name/value pairs. Is there a way to modify my jq script to do this? – Brett Apr 06 '17 at 09:38
1

@Brett `jq` will normally preserve the input order of keys in the objects, unless the `--sort-keys` (`-S`) option is specified. Unfortunately, the `walk()` function will reconstruct objects as it descents the object tree recursively, losing the input order of keys in the process. Not sure on if it can be rewritten to avoid that. – zeppelin Apr 06 '17 at 10:47

sort json document using jq

1 Answers1