Using SQL to query JSON

Tantalum

Tantalum

Final Approach
Joined
Feb 22, 2017
Messages
9,247
Display Name
Display name:
San_Diego_Pilot
For reasons that will otherwise remain unstated.. I have data living in JSON that is not currently being ported into any existing SQL table (sigh)

I need to pull data from the JSON, this is possible with SQL if you know the schema structure (which I do).. however I was warned by a dev that this was bad and potentially lethal to the system

I'm dubious of that claim since the data I am getting is coming off of completed and locked forms.. so the schema and data within the schema in theory will never change.. IE, no real deadlock threat

What am I missing?
 
If it’s not in the db, I’m a bit confused on why you’d use sql. Is this one ginormous Json or something? I don’t really understand why you’d need sql to parse data out of a json message.
 
Don't really understand the question. Do you have a big JSON file and want to query it with SQL? Does there even exist a tool to do that? Efficient use of such a tool would depend upon how the tool is designed. But SQL is a language designed to query a relational database, whose data storage system is designed to be used with SQL efficiently, not so with JSON. Unless the entire JSON file is small and can fit in memory, I'm guessing it would take a large amount of processing power to query it.
 
Tantalum said:
For reasons that will otherwise remain unstated.. I have data living in JSON that is not currently being ported into any existing SQL table (sigh)

I need to pull data from the JSON, this is possible with SQL if you know the schema structure (which I do).. however I was warned by a dev that this was bad and potentially lethal to the system

I'm dubious of that claim since the data I am getting is coming off of completed and locked forms.. so the schema and data within the schema in theory will never change.. IE, no real deadlock threat

What am I missing?
Click to expand...

Are you wanting to import the JSON data into SQL tables and query it there?

Also which SQL are you using?
 
^^^ I'm with these guys - not sure what you mean "query JSON with SQL".

If the JSON string is stored in a SQL database field, then you may be able to parse the JSON out depending on which DB you're using. If you're wanting to import the JSON string into a database or otherwise parse the JSON string into objects, then you'll need something other than SQL (python comes to mind) to parse it out.
 
Sounds like you need to hire a developer to normalize your json into your sql structure.
Or you could just throw it in a NoSQL document store and query it that way, which works well as long as you don't want to join different document types in your query.
 
Assuming you have a text file with a number of JSON strings in it, you should be able to use an import tool to save it to a SQL table, and assuming your SQL has some JSON tools, parse it into individual values which you can store in a database table, and then use SQL to query that table. In MS SQL Server, you can use the OPENJSON function to parse the JSON and then use what it returns to write to your data table.
 
In June, a third party that I get data from switched their format from a flat file to JSON. I think they figured that it would be easier to parse—they even provide the Python code for reading it. For stupid reasons, I need to do this in perl. Unlike other languages, there isn’t a perl method to easily decode JSON so I string together a bunch of methods with some array looping until I get the data into a form that I can insert into my tables.

I can’t see why getting data in any particular format would be ”lethal to the system”. As long as the data is in the same format every time you read it it shouldn’t be an issue.
 
UngaWunga said:
Sounds like you need to hire a developer to normalize your json into your sql structure.
Click to expand...

I'm hearing that he is the developer.

Is the warning about adding data to a product table? I'd echo the concern about polluting the database. But if you're creating new tables then I don't know what the concern is. I think it would be a good idea to insert to new tables in your import so you can check all your data is correct.

Google tells me Python is a pretty good tool for data conversions. This is a great problem to apply to learning it. I've used python a few times and my memory is that it's very Perl-ish.
https://blog.sqlizer.io/posts/convert-json-to-sql/
 
Ha, I used to do data conversion as a part of my job [application developer] for USAF and other government entities. One job required converting a large PICK database, one required getting data from an obscure Apple system (the only Apple computer in the entire department). We ended up with everything in Oracle. I'm so happy that I don't do that sort of thing now. But in all seriousness, it doesn't really sound like a big project; I'd convert/import the data into some more common relational database and go from there. (I still have an Oracle box with a dummy of a certain county gov't database, used for updating their software and report creation, which was written by my company before my beard was grey.) I never messed with JSON, but a quickie Wiki tells me that it shouldn't be an issue, especially if you have some C programming skills.
 
JScarry said:
In June, a third party that I get data from switched their format from a flat file to JSON. I think they figured that it would be easier to parse—they even provide the Python code for reading it. For stupid reasons, I need to do this in perl. Unlike other languages, there isn’t a perl method to easily decode JSON so I string together a bunch of methods with some array looping until I get the data into a form that I can insert into my tables.

I can’t see why getting data in any particular format would be ”lethal to the system”. As long as the data is in the same format every time you read it it shouldn’t be an issue.
Click to expand...

Perl doesn't do JSON easily? You're joking right? Look on CPAN. Rolling your own parser is dumb.
 
I’d use node-js myself. Lol
 
Just a basic day in postgreSQL land. Which database engine?

Normalizing his JSON defeats the purpose of storing JSON in a db to begin with -- ability to change schema rapidly

I've never heard of using a json field query being lethal to any system. The idea that the query tools even exist means they're a database function. Does your consulting dev sell slurpees as a side hustle?
 
schmookeeg said:
Just a basic day in postgreSQL land. Which database engine?

Normalizing his JSON defeats the purpose of storing JSON in a db to begin with -- ability to change schema rapidly
Click to expand...
He said it wasn’t in the dB, that’s why I’m confused why he’d use sql in the first place.
 
Salty said:
He said it wasn’t in the dB, that’s why I’m confused why he’d use sql in the first place.
Click to expand...

I assumed the json was "in the vicinity of the database" since he's using that tool. After a re-read, yeah, I see your construct on the thing.

Clarify, OP so that we may re-architect your software for you and potentially chide you in the process :D
 
Sac Arrow said:
I wish Google had a translator for this.
Click to expand...
"I have data living in JSON that is not currently being ported into any existing SQL table (sigh)"

JSON (Javascript Object Notation) is a way of encoding data so that each item has a descriptive label. For example, say you have a list of airplanes with their manufacturers, model numbers, and engine type. A JSON file with that information may look like this:

Manufacturer : "Cessna", Model: "172N", Engine: "O-320"
Manufacturer: "Grumman", Model: "AA1", Engine "O-235"
Manufacturer: "Grumman", Model: "AA5B", Engine "O-360"
etc

SQL (Structured Query Language) was originally a language used to manipulate data. The term "SQL" is quite often used to describe a database that uses the SQL language. When @Tantalum said the JSON data he has was not ported to any table, he was referring to a database program that they have at his office, and that the JSON data was not stored in a database table. SQL databases store data in "tables", visualize a table as being like a spreadsheet. So for that same JSON for the airplane database, it would have three columns and three rows, with the columns being "Manufacturer", "Model", and "Engine".

Once you have the JSON data ported into the SQL table, you can then write a query (which is a sort of a computer program) to get various information from the table. Let's say we imported the JSON data into a table named "Airplanes". If you wanted a list of the models and engines for the Grummans in that table, you'd enter

SELECT Model, Engine
FROM Airplanes
WHERE Manufacturer = 'Grumman'

you'd get back

AA1 O-235
AA5B O-360

What @Tantalum wants to do remains to be seen. If he's asking if there is a program that uses SQL syntax to return data from a JSON formatted file, I don't know of one. If it were me, I'd pull it into a SQL database and query it there, since I have those tools available.
 
FormerHangie said:
"I have data living in JSON that is not currently being ported into any existing SQL table (sigh)"

JSON (Javascript Object Notation) is a way of encoding data so that each item has a descriptive label. For example, say you have a list of airplanes with their manufacturers, model numbers, and engine type. A JSON file with that information may look like this:

Manufacturer : "Cessna", Model: "172N", Engine: "O-320"
Manufacturer: "Grumman", Model: "AA1", Engine "O-235"
Manufacturer: "Grumman", Model: "AA5B", Engine "O-360"
etc

SQL (Structured Query Language) was originally a language used to manipulate data. The term "SQL" is quite often used to describe a database that uses the SQL language. When @Tantalum said the JSON data he has was not ported to any table, he was referring to a database program that they have at his office, and that the JSON data was not stored in a database table. SQL databases store data in "tables", visualize a table as being like a spreadsheet. So for that same JSON for the airplane database, it would have three columns and three rows, with the columns being "Manufacturer", "Model", and "Engine".

Once you have the JSON data ported into the SQL table, you can then write a query (which is a sort of a computer program) to get various information from the table. Let's say we imported the JSON data into a table named "Airplanes". If you wanted a list of the models and engines for the Grummans in that table, you'd enter

SELECT Model, Engine
FROM Airplanes
WHERE Manufacturer = 'Grumman'

you'd get back

AA1 O-235
AA5B O-360

What @Tantalum wants to do remains to be seen. If he's asking if there is a program that uses SQL syntax to return data from a JSON formatted file, I don't know of one. If it were me, I'd pull it into a SQL database and query it there, since I have those tools available.
Click to expand...

You have to understand - my tech savvy ended at Windows 3.1.
 
Sac Arrow said:
You have to understand - my tech savvy ended at Windows 3.1.
Click to expand...
Just like Microsoft! Lol j/k in reality, they stole everything up until then. Lol
 
My favorite question as a consultant... "What is the actual problem you are trying to solve?" And then charging them large amounts of money to solve it.
 
Depending on the database and complexity/size of JSON, you can certainly import data from JSON file into a permanent or temporary table and then use TSQL to manipulate it. However, this(the importing part) is fraught with potential issues. This is something that should be done by a real programming language that can easily manipulate both JSON and DB in much better and more predictable ways than SQL that is hardly designed for this.

It would probably take less time to write such program in practically any modern programming language than to iron out all the bugs and data issues in trying to use SQL/JSON approach in all but very simple JSON files.

So yeah, listen to your developer :)
 
dmspilot said:
Perl doesn't do JSON easily? You're joking right? Look on CPAN. Rolling your own parser is dumb.
Click to expand...

There are literally thousands of JSON parsers in CPAN. And since no one I know uses perl, I rely on places like StackOverflow where most of the answers depend on you having an intimate knowledge of perl. I tried the ones they suggest but most of the ones I tried either don’t work, have dependencies that don’t work, or work with an older version of perl but not the version we’re using. I finally found one that would at least output the data into an array of arrays that I could decode.

If perl does JSON easily, they certainly don’t make it easy to discover.
 
JScarry said:
There are literally thousands of JSON parsers in CPAN. And since no one I know uses perl, I rely on places like StackOverflow where most of the answers depend on you having an intimate knowledge of perl. I tried the ones they suggest but most of the ones I tried either don’t work, have dependencies that don’t work, or work with an older version of perl but not the version we’re using. I finally found one that would at least output the data into an array of arrays that I could decode.

If perl does JSON easily, they certainly don’t make it easy to discover.
Click to expand...

There is literally a module called JSON.
 
dmspilot said:
There is literally a module called JSON.
Click to expand...
And according to the docs it's even in core as of 5.14 (sure, it's the slower pure-perl version, but it's plenty good enough to parse into perl structures to use native perl on)
 
JScarry said:
There are literally thousands of JSON parsers in CPAN. And since no one I know uses perl, I rely on places like StackOverflow where most of the answers depend on you having an intimate knowledge of perl. I tried the ones they suggest but most of the ones I tried either don’t work, have dependencies that don’t work, or work with an older version of perl but not the version we’re using.
Click to expand...
And this exact thing is why I gave up trying to do anything at all - ever - in Perl. Seems Perl applicaitons work just fine - as long as they're running on the original developer's computer, and don't nobody touch nuthin'.
 
DaleB said:
And this exact thing is why I gave up trying to do anything at all - ever - in Perl. Seems Perl applicaitons work just fine - as long as they're running on the original developer's computer, and don't nobody touch nuthin'.
Click to expand...
Even if you can run it somewhere else, nobody but the author will ever be able to change it without breaking it.
 
Salty said:
Even if you can run it somewhere else, nobody but the author will ever be able to change it without breaking it.
Click to expand...
That's why I wrote my whole site in perl... Job security. Well, except for the SQL bits, the Java bits, the Python bits and a bunch of shell scripts.

And the fact I make absolutely no money from it.
 
EdFred said:
So many nerds. So many asses to kick.
:D
Click to expand...

16df1f63ce1fffd06c38b5b921fa4756.jpg
 
Salty said:
Even if you can run it somewhere else, nobody but the author will ever be able to change it without breaking it.
Click to expand...
I was never successful in even step 1 - getting it to run anywhere else. The simplest thing turned into cascading dependencies, ultimately ending in, “Well, you just can’t get there from here”.

I never have been a “real” programmer, just a hack. I need to learn Python.
 
DaleB said:
And this exact thing is why I gave up trying to do anything at all - ever - in Perl. Seems Perl applicaitons work just fine - as long as they're running on the original developer's computer, and don't nobody touch nuthin'.
Click to expand...

I believe it has been described as a Write Only Language.
 
So am I to understand the JSON database is live and you're needing to continually update stuff from it into the SQL db?

Because if not it would seem like a pretty simple thing to just dump it all into a flat file then write a script or small application to import it all into SQL in an SQL friendly format.... maybe that's too much of a sledgehammer approach but I like keeping it simple.
 
I had never used Perl but I managed to make minor updates to a website and associated backend files for three years without too much trouble. The language itself is easy to read and it wasn’t too difficult to make updates to the scripts and web pages.

I’ve done a bunch of server migrations and while they are all have their challenges, it’s generally not that hard. Not the case with this server. I was migrating from an old Dell machine running Ubuntu 10.4 LTS to a Linode instance running Ubuntu 18.04 LTS. Someone back in the late 90s decided that it was a good idea to run the entire site using APACHE::ASP and getting it to run on the new server was a challenge. I probably have more modules installed than I need and modules installed in the wrong place but it works. I’m afraid to change anything since I have no idea why it is working.
 
There are a million ways to skin this cat... Choosing the correct direction is why you need a developer with experience. Answering the question can't happen with more info. But good luck. Maybe post a question on Stackoverflow... there's a lot of "developers" that code solely based on info there.
 
Personally, I think the angle of attack indicator could be used to parse the sol into the jdamn when integrated with pong 2.0.

Use at your own risk....
 
I mostly love that there have been 37 replies since @Tantalum made the OP, and there's every response from "easy, just learn 2 code" all the way to "impossible: syntax error", lol. Maybe he'll be back along to add some color to his original request.
 
SoonerAviator said:
I mostly love that there have been 37 replies since @Tantalum made the OP, and there's every response from "easy, just learn 2 code" all the way to "impossible: syntax error", lol. Maybe he'll be back along to add some color to his original request.
Click to expand...
I’m calling troll. Posts a confusing question and then never comes back.
 
You must log in or register to reply here.
Back
Top