How to get a summary of the contents of a JSON file ?

[janpfeifer]

There is this undocumented JSON file that has > 1M lines that I have to make sense of.

Is there a type of query that will show the JSON contents but limited to N (N=3?) elements at most of any array / object, and list the total number of elements ... just so I can understand what's in it ?

I also tried to dig into the JSON file one level at a time, but I didn't find a maxdepth option, or a keys(.) operator, that would only list the keys of the object but not its contents. Then I could try to navigate interactively.

Likely I'm going this the wrong way, and there is another approach to this. Any pointers would be most welcome 😄

thanks in advance!

[wader]

Hey! maybe have a look at these:

• https://github.com/hgrsd/drivel
• https://github.com/tomnomnom/gron
• https://github.com/ynqa/jnv

Some snippets you do with jq:

# ugly summerise unique paths
$ jq -r '[path(..) | map(if type == "number" then "[]" end) | join(".")] | unique[]' file.json

# truncate arrays
$ jq '(.. | arrays) = .[0:2]' file.json

I might dump more examples when i have time... i spend quite a lot of time digging thru files in various formats 😄

[pkoppstein]

I wasn't familiar with drivel and so installed it (on a Mac), but so far drivel describe just produces (dare I say it) drivel. The documentation is underwhelming. I'm wondering what I'm missing.

By the way, some time ago you wondered aloud about some aspects of stackoverflow.com. Whatever else might be said, I think it's handy to know that there's a friendly community of jq users there, and that as a rule, it makes much more sense to have usage questions asked there than here, from multiple points of view too. Of course things could and no doubt will change... all the more reason to support the jq community at stackoverflow.com :-)

[wader]

I wasn't familiar with drivel and so installed it (on a Mac), but so far drivel describe just produces (dare I say it) drivel. The documentation is underwhelming. I'm wondering what I'm missing.

Hmm weird, quite sure it gave a summary/scheme for me when using drivel describe so i tried latest main, and it seem to work:

 $ cat github-repos.json | cargo run -- describe
[
  {
    "homepage": nullable string (0),
    "private": boolean,
    "pushed_at": string (datetime - ISO 8601),
    "ssh_url": string (28-36),
    "milestones_url": string (url),
    "archived": boolean,
    "statuses_url": string (url),
 ...
    "topics": [
      string (2-20)
    ] (0-4),
...
] (30)

How did it behave for you?

By the way, some time ago you wondered aloud about some aspects of stackoverflow.com. Whatever else might be said, I think it's handy to know that there's a friendly community of jq users there, and that as a rule, it makes much more sense to have usage questions asked there than here, from multiple points of view too. Of course things could and no doubt will change... all the more reason to support the jq community at stackoverflow.com :-)

👍 that is good to know

[pkoppstein]

Regarding Drivel - I made the mistake of giving it a couple of top-level objects, and didn't pay proper attention to the error message. Sorry for the noise. It's a pity it doesn't produce JSON.

[janpfeifer]

Thank you for the links @wader. All are great tools -- in particular drivel would have been what I was looking for. I ended up searching around and using fx.wtf, which allowed me in an instance to browse through the json tree -- but it didn't give me the counts, to understand which ones were the most dense arrays/maps.

[pkoppstein]

Welcome to the wonderful world of jq! jq is very handy for the type of challenge you describe, and I would say that the investment in learning jq that might be involved is very likely to be worth your while.

To address your question more directly, let me suggest you start with things like jq type and jq length, and generally proceed in accordance with the results you get. If, for example, you find the file contains multiple top-level objects, you could view the keys of each by jq -c keys_unsorted, though if it contains zillions of such objects, you'll want a better way to summarize the information, e.g. perhaps jq -n '[inputs | keys_unsorted?] | unique'.

If you find the file has a very large number of top-level JSON values, you could determine their types by:

jq -n '[inputs | type] | unique' 

To replace each array by its length, you could write: jq 'walk(if type == "array" then length end)'

If you want to infer a schema for the entire file, then there are ways to do that too. See e.g. my own schema.jq
at https://gist.github.com/pkoppstein/a5abb4ebef3b0f72a6ed. You might also like to try DuckDB, which has a JSON "sniffer". It's easy to download and even easier to use. You could start with:

create or replace table t as (from read_json('INPUT.JSON')); 
describe t;
summarize t;

I'd suggest that after you're more familiar with jq and your file, you pose some more targeted questions on
stackoverflow.com using the jq tag.