How to navigate inside JSON hierarchy?

[aborruso]

Hi,
I have the input JSON you at the bottom.

Using both jq and miller, I run <input.json | jq '."@graph"' | mlr --j2c cat to have this simple CSV that has the 4 fields of the JSON.

@id,@type,idBene,mBene,sBene,iterAmministrativoAttivo
impresa:LgU0qvE+USpLWpu+dbQePfm7KJfG8SOgr/YdHGlu9VI=,impresa,2030,A-PA-301560,A-PA-2030-S,DESTINATO
impresa:23sS3uBiY1ZUgO9zTlRswfm7KJfG8SOgr/YdHGlu9VI=,impresa,2035,A-BN-312138,A-BN-2035-S,DESTINATO
impresa:Rkp64uKWWg4624pPVH0x2Q==,impresa,2036,A-BN-312139,A-BN-2036-S,DESTINATO
impresa:G8GiEGax1LSanPDijSE4Jg==,impresa,2037,A-BN-312140,A-BN-2037-S,IN GESTIONE

I would like to use only Miller. But if I run <input.json | mlr --j2c cut -f "@graph" I have

@graph.1.@id,@graph.1.@type,@graph.1.idBene,@graph.1.mBene,@graph.1.sBene,@graph.1.iterAmministrativoAttivo,@graph.2.@id,@graph.2.@type,@graph.2.idBene,@graph.2.mBene,@graph.2.sBene,@graph.2.iterAmministrativoAttivo,@graph.3.@id,@graph.3.@type,@graph.3.idBene,@graph.3.mBene,@graph.3.sBene,@graph.3.iterAmministrativoAttivo,@graph.4.@id,@graph.4.@type,@graph.4.idBene,@graph.4.mBene,@graph.4.sBene,@graph.4.iterAmministrativoAttivo
impresa:LgU0qvE+USpLWpu+dbQePfm7KJfG8SOgr/YdHGlu9VI=,impresa,2030,A-PA-301560,A-PA-2030-S,DESTINATO,impresa:23sS3uBiY1ZUgO9zTlRswfm7KJfG8SOgr/YdHGlu9VI=,impresa,2035,A-BN-312138,A-BN-2035-S,DESTINATO,impresa:Rkp64uKWWg4624pPVH0x2Q==,impresa,2036,A-BN-312139,A-BN-2036-S,DESTINATO,impresa:G8GiEGax1LSanPDijSE4Jg==,impresa,2037,A-BN-312140,A-BN-2037-S,IN GESTIONE

How to navigate inside the JSON input, to arrive to the root of @graph, and to cut file content starting from it?
Or auto to simply flatten this JSON to the first example CSV?

Thank you

{
  "@context": {
    "dsbase": "https://aziendeconfiscate.infocamere.it/odacWeb/",
    "impresa": "dsbase:impresa/",
    "idBene": "impresa:idBene",
    "mBene": "impresa:mBene",
    "sBene": "impresa:sBene",
    "iterAmministrativoAttivo": "tipoIterAmministrativoAttivo"
  },
  "@graph": [
    {
      "@id": "impresa:LgU0qvE+USpLWpu+dbQePfm7KJfG8SOgr/YdHGlu9VI=",
      "@type": "impresa",
      "idBene": "2030",
      "mBene": "A-PA-301560",
      "sBene": "A-PA-2030-S",
      "iterAmministrativoAttivo": "DESTINATO"
    },
    {
      "@id": "impresa:23sS3uBiY1ZUgO9zTlRswfm7KJfG8SOgr/YdHGlu9VI=",
      "@type": "impresa",
      "idBene": "2035",
      "mBene": "A-BN-312138",
      "sBene": "A-BN-2035-S",
      "iterAmministrativoAttivo": "DESTINATO"
    },
    {
      "@id": "impresa:Rkp64uKWWg4624pPVH0x2Q==",
      "@type": "impresa",
      "idBene": "2036",
      "mBene": "A-BN-312139",
      "sBene": "A-BN-2036-S",
      "iterAmministrativoAttivo": "DESTINATO"
    },
    {
      "@id": "impresa:G8GiEGax1LSanPDijSE4Jg==",
      "@type": "impresa",
      "idBene": "2037",
      "mBene": "A-BN-312140",
      "sBene": "A-BN-2037-S",
      "iterAmministrativoAttivo": "IN GESTIONE"
    }
  ]
}

[johnkerl]

@aborruso sorry for the delay in getting back to you! :^/

How about

mlr --from input.json --j2c put -q '
  for (item in $["@graph"]) {
    emit item
  }
'

[aborruso]

Hi @johnkerl
this 6 release is really great, but I have an empty head and i didn't quite understand how to use it with json files. I can't even do simple things :(

For me it would be great to have some recipes page, some examples page related with some examples for empty heads like mine :)

Below some example of basic things that I would like to learn how to deal with Miller.

This is an example input json file

{
  "version": 0.1,
  "generator": "andy",
  "metadata": {
    "timestamp_base": "2022-02-13T22:40:12.0000000Z",
    "copyright": "CC-BY license"
  },
  "elements": [
    {
      "type": "person",
      "id": 1,
      "tags": {
        "karma_level": 1000000000,
        "name": "John",
        "surname": "Kerl",
        "country": "USA",
        "country:it": "Stati Uniti",
        "project": "Miller",
        "about": "A too kind person"
      }
    },
    {
      "type": "person",
      "id": 2,
      "tags": {
        "karma_level": 1,
        "name": "Andrea",
        "surname": "Borruso",
        "country": "Italy",
        "country:it": "Italia",
        "project": "Miller promoting"
      }
    }
  ]
}

My first goal is to have this output:

version	generator	timestamp_base	copyright
0.1	andy	2022-02-13T22:40:12.0000000Z	CC-BY license

The way to do, it's not

mlr --j2c cut -f version,generator,"metadata.timestamp_base","metadata.copyright" sample.json

My second goal is to have

id	name	country:it
1	John	Stati Uniti
2	Andrea	Italia

If I run

mlr --j2c put -q 'for (item in $["elements"]) {emit item}' then unsparsify sample.json

it seem that unsparsify does not work properly, because I have two times the heading

type,id,tags.karma_level,tags.name,tags.surname,tags.country,tags.country:it,tags.project,tags.about
person,1,1000000000,John,Kerl,USA,Stati Uniti,Miller,A too kind person

type,id,tags.karma_level,tags.name,tags.surname,tags.country,tags.country:it,tags.project
person,2,1,Andrea,Borruso,Italy,Italia,Miller promoting

If I run

mlrgo --j2c put -q 'for (item in $["elements"]) {emit item}' then cut -f id sample.json

I have the id field

id
1
2

But if I add the other two fields I obtain only the id. Running

mlr --j2c put -q 'for (item in $["elements"]) {emit item}' then cut -f id,tags.name,"tags.country:it" sample.json

I have again

id
1
2

If I can figure out how, I volunteer to write a FAQ item.

I know this is not the way to refer to a field. I wrote those commands just to show my goal.

Thank you

[aborruso]

@johnkerl reading you here I want to add that for me it's very interesting to do it in DSL too. I have written only verbs example, because I am very poor with DSL.

I want to learn to think about it and doing it in DSL, with these simple examples, would make me learn a lot