Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Run SQL on JSON files without any data loads

citusdata.com

Post reply on HN
Print view View original

1–10 of 37 posts

Re: Run SQL on JSON files without any data loads

#3

Post by knowtheory »

I am mystified as to when this tool would be useful (can anybody else think of a practical use case?).

You can run SQL queries on individual JSON files... but you have to have PostgreSQL installed already.

And you can't run it against multiple JSON files, only one, with a single JSON object at the root.

Re: Run SQL on JSON files without any data loads

#5

Post by languagehacker »

I kind of think the more common use case is accessing JSON data _without_ defining a schema. In fact I wouldn't mind loading the data so long as it doesn't persist for too long. If only there was a schemaless database that allowed you to set a time to live on the JSON objects you load into it... that would be humongous.

Re: Run SQL on JSON files without any data loads

#7

Post by seppo0010 »

knowtheory wrote: post #3

I am mystified as to when this tool would be useful (can anybody else think of a practical use case?). You can run SQL queries on individual JSON files... but you have to have PostgreSQL installed already. And you can't run it against multiple JSON files, only one, with a single JSON object at the root.

The only valid use case I can think of is for preloaded data for automatized tests.

Re: Run SQL on JSON files without any data loads

#8

Post by mortov »

I remember when it caught my eye as Postgres95 and I've always preferred it, even in the face of the huge surge for MySQL which left PostgreSQL looking like an also-ran for quite while.

The reality is it is one of the most amazing open source projects I know - enough to rival Linux itself for me.

These little snippets of PostgreSQL features and facilities which keep being posted give just a tiny hint of the truly enterprise grade features and facilities it has - I just wish I still had as much time to keep on top of it as I once did but I still keep it as my go-to database for project development. I've even dropped it in to replace full blown Oracle systems for serious cost savings.

The developers should be receiving far more kudos than they do.

Re: Run SQL on JSON files without any data loads

#9

Post by chaz »

knowtheory wrote: post #3

I am mystified as to when this tool would be useful (can anybody else think of a practical use case?). You can run SQL queries on individual JSON files... but you have to have PostgreSQL installed already. And you can't run it against multiple JSON files, only one, with a single JSON object at the root.

Keep in mind that this blog post was written by a company builds a product that sits on top of PostgreSQL. So for the intended audience, it's completely relevant.

A good use case is exploring external APIs, many of which come back as JSON. This might be a faster awy to figure out what you're dealing with: how large, how many, unique values, mins, maxes, etc. It could be especially useful if you need to match it to existing keys or metadata that you already have in the same database. To solve this same problem before, I've written lots of one-off scripts that convert the JSON into a CSV so that I can view it in Excel. This may or may not help me there, but good to see another tool.

Re: Run SQL on JSON files without any data loads

#10

Post by d4nt »

I don't make a habit of spamming discussion threads but... my product QueryTree (http://querytreeapp.com) will load JSON files even if they have lists at the root, you can use the Append tool to bring multiple files together, and you can then use Filter, Sort, Join and Group without ever having to define a schema.

Admittedly, it's aimed at novice users so if you're comfortable with SQL it may feel limiting.

Post reply on HN