Using SQL to query JSON

Discussion in 'Hangar Talk' started by Tantalum, Sep 24, 2019.

  1. Tantalum

    Tantalum En-Route

    Joined:
    Feb 22, 2017
    Messages:
    4,344
    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?
     
  2. Salty

    Salty En-Route PoA Supporter

    Joined:
    Dec 21, 2016
    Messages:
    4,768
    Location:
    FL
    Display Name:

    Display name:
    Salty
    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.
     
  3. dmspilot

    dmspilot En-Route

    Joined:
    Oct 20, 2006
    Messages:
    3,633
    Display Name:

    Display name:
    Display name:
    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.
     
  4. FormerHangie

    FormerHangie En-Route

    Joined:
    Oct 28, 2013
    Messages:
    2,739
    Location:
    Roswell, GA
    Display Name:

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

    Also which SQL are you using?
     
  5. CJones

    CJones Final Approach

    Joined:
    Mar 14, 2005
    Messages:
    5,027
    Location:
    Jawjuh
    Display Name:

    Display name:
    uHaveNoIdea
    ^^^ 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.
     
  6. CltFlyBoy

    CltFlyBoy Filing Flight Plan

    Joined:
    May 17, 2006
    Messages:
    18
    Location:
    Charlotte, NC
    Display Name:

    Display name:
    Henry Webb
  7. UngaWunga

    UngaWunga Cleared for Takeoff

    Joined:
    Oct 27, 2014
    Messages:
    1,443
    Display Name:

    Display name:
    UngaWunga
    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.
     
  8. FormerHangie

    FormerHangie En-Route

    Joined:
    Oct 28, 2013
    Messages:
    2,739
    Location:
    Roswell, GA
    Display Name:

    Display name:
    FormerHangie
    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.
     
  9. JScarry

    JScarry Pre-takeoff checklist

    Joined:
    Jun 15, 2008
    Messages:
    196
    Display Name:

    Display name:
    JScarry
    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.
     
  10. bflynn

    bflynn Final Approach

    Joined:
    Apr 24, 2012
    Messages:
    5,241
    Location:
    Fuquay Varina, NC
    Display Name:

    Display name:
    Brian Flynn
    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/
     
  11. Sac Arrow

    Sac Arrow Touchdown! Greaser!

    Joined:
    May 11, 2010
    Messages:
    16,304
    Location:
    Oakland, CA
    Display Name:

    Display name:
    Full Send Mode
    I wish Google had a translator for this.
     
  12. Kenny Phillips

    Kenny Phillips En-Route

    Joined:
    Jul 29, 2018
    Messages:
    2,502
    Display Name:

    Display name:
    Kenny Phillips
    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.
     
  13. dmspilot

    dmspilot En-Route

    Joined:
    Oct 20, 2006
    Messages:
    3,633
    Display Name:

    Display name:
    Display name:
    Perl doesn't do JSON easily? You're joking right? Look on CPAN. Rolling your own parser is dumb.
     
    UngaWunga and chartbundle like this.
  14. Salty

    Salty En-Route PoA Supporter

    Joined:
    Dec 21, 2016
    Messages:
    4,768
    Location:
    FL
    Display Name:

    Display name:
    Salty
    I’d use node-js myself. Lol
     
  15. schmookeeg

    schmookeeg Cleared for Takeoff

    Joined:
    Nov 6, 2008
    Messages:
    1,212
    Location:
    Hipsterdelphia PDX
    Display Name:

    Display name:
    Mike Brannigan
    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?
     
    deonb likes this.
  16. Salty

    Salty En-Route PoA Supporter

    Joined:
    Dec 21, 2016
    Messages:
    4,768
    Location:
    FL
    Display Name:

    Display name:
    Salty
    He said it wasn’t in the dB, that’s why I’m confused why he’d use sql in the first place.
     
  17. schmookeeg

    schmookeeg Cleared for Takeoff

    Joined:
    Nov 6, 2008
    Messages:
    1,212
    Location:
    Hipsterdelphia PDX
    Display Name:

    Display name:
    Mike Brannigan
    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
     
    FormerHangie likes this.
  18. FormerHangie

    FormerHangie En-Route

    Joined:
    Oct 28, 2013
    Messages:
    2,739
    Location:
    Roswell, GA
    Display Name:

    Display name:
    FormerHangie
    "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.
     
  19. Sac Arrow

    Sac Arrow Touchdown! Greaser!

    Joined:
    May 11, 2010
    Messages:
    16,304
    Location:
    Oakland, CA
    Display Name:

    Display name:
    Full Send Mode
    You have to understand - my tech savvy ended at Windows 3.1.
     
  20. Salty

    Salty En-Route PoA Supporter

    Joined:
    Dec 21, 2016
    Messages:
    4,768
    Location:
    FL
    Display Name:

    Display name:
    Salty
    Just like Microsoft! Lol j/k in reality, they stole everything up until then. Lol
     
  21. chartbundle

    chartbundle Cleared for Takeoff

    Joined:
    Sep 26, 2011
    Messages:
    1,295
    Location:
    State of Confusion
    Display Name:

    Display name:
    chartbundle
    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.
     
    TCABM and Salty like this.
  22. genna

    genna Cleared for Takeoff

    Joined:
    Feb 5, 2015
    Messages:
    1,255
    Display Name:

    Display name:
    ТУ-104
    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 :)
     
  23. JScarry

    JScarry Pre-takeoff checklist

    Joined:
    Jun 15, 2008
    Messages:
    196
    Display Name:

    Display name:
    JScarry
    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.
     
  24. dmspilot

    dmspilot En-Route

    Joined:
    Oct 20, 2006
    Messages:
    3,633
    Display Name:

    Display name:
    Display name:
    There is literally a module called JSON.
     
    schmookeeg likes this.
  25. chartbundle

    chartbundle Cleared for Takeoff

    Joined:
    Sep 26, 2011
    Messages:
    1,295
    Location:
    State of Confusion
    Display Name:

    Display name:
    chartbundle
    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)
     
  26. EdFred

    EdFred Touchdown! Greaser! PoA Supporter

    Joined:
    Feb 25, 2005
    Messages:
    24,057
    Location:
    Michigan
    Display Name:

    Display name:
    Ed Frederick
    So many nerds. So many asses to kick.
    :D
     
    david.h, rtk11, schmookeeg and 2 others like this.
  27. flhrci

    flhrci Final Approach

    Joined:
    Jan 26, 2007
    Messages:
    5,152
    Location:
    Ashville, OH
    Display Name:

    Display name:
    David
    You should be good then!
     
  28. flhrci

    flhrci Final Approach

    Joined:
    Jan 26, 2007
    Messages:
    5,152
    Location:
    Ashville, OH
    Display Name:

    Display name:
    David
    ROFLMAO!
     
  29. DaleB

    DaleB En-Route

    Joined:
    Aug 24, 2011
    Messages:
    4,076
    Location:
    Omaha, NE
    Display Name:

    Display name:
    DaleB
    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'.
     
  30. Salty

    Salty En-Route PoA Supporter

    Joined:
    Dec 21, 2016
    Messages:
    4,768
    Location:
    FL
    Display Name:

    Display name:
    Salty
    Even if you can run it somewhere else, nobody but the author will ever be able to change it without breaking it.
     
  31. chartbundle

    chartbundle Cleared for Takeoff

    Joined:
    Sep 26, 2011
    Messages:
    1,295
    Location:
    State of Confusion
    Display Name:

    Display name:
    chartbundle
    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.
     
    Salty likes this.
  32. SoonerAviator

    SoonerAviator En-Route PoA Supporter

    Joined:
    Jul 21, 2014
    Messages:
    4,779
    Location:
    Broken Arrow, OK
    Display Name:

    Display name:
    SoonerAviator
    [​IMG]
     
  33. DaleB

    DaleB En-Route

    Joined:
    Aug 24, 2011
    Messages:
    4,076
    Location:
    Omaha, NE
    Display Name:

    Display name:
    DaleB
    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.
     
  34. PeterNSteinmetz

    PeterNSteinmetz Cleared for Takeoff

    Joined:
    Sep 9, 2015
    Messages:
    1,013
    Location:
    Tempe, AZ
    Display Name:

    Display name:
    PeterNSteinmetz
    I believe it has been described as a Write Only Language.
     
    DaleB likes this.
  35. cowman

    cowman En-Route PoA Supporter

    Joined:
    Aug 12, 2012
    Messages:
    4,073
    Location:
    Danger Zone
    Display Name:

    Display name:
    Cowman
    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.
     
  36. JScarry

    JScarry Pre-takeoff checklist

    Joined:
    Jun 15, 2008
    Messages:
    196
    Display Name:

    Display name:
    JScarry
    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.
     
  37. UngaWunga

    UngaWunga Cleared for Takeoff

    Joined:
    Oct 27, 2014
    Messages:
    1,443
    Display Name:

    Display name:
    UngaWunga
    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.
     
  38. Lndwarrior

    Lndwarrior Pre-takeoff checklist

    Joined:
    Jan 15, 2009
    Messages:
    375
    Display Name:

    Display name:
    Gary
    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....
     
    TCABM likes this.
  39. SoonerAviator

    SoonerAviator En-Route PoA Supporter

    Joined:
    Jul 21, 2014
    Messages:
    4,779
    Location:
    Broken Arrow, OK
    Display Name:

    Display name:
    SoonerAviator
    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.
     
    overdrive148 likes this.
  40. Salty

    Salty En-Route PoA Supporter

    Joined:
    Dec 21, 2016
    Messages:
    4,768
    Location:
    FL
    Display Name:

    Display name:
    Salty
    I’m calling troll. Posts a confusing question and then never comes back.
     
    Justin M, flhrci and SoonerAviator like this.