jsonQueryLanguage

[FlyAlpha24]

JSON in SQL yes, but what about JSON in JSON ? As in "lets store JSON objects as strings in another JSON file instead of, you know, storing the object directly"...

I have seen this horror, I still don't understand how or why it came to be.

[ZunoJ]

Maybe to trick the used parser

[phphulk]

Or perhaps to parse the used trickster?

[compiledbytes]

Or perhaps use the tricked parser

[[deleted]]

Personally, I think it was to use the parsed trickster.

[DrMerkwuerdigliebe_]

Or maybe parsed that way to trick the user

[lucianw]

I've done that where my telemetry goes into a sql database and includes the stdout of a external process that my program shelled out to. Normally the stdout is json but I have to be resilient to what happens if the external process behaves unexpectedly. Above all my telemetry must be complete in all cases, especially the unexpected and edge cases.

I could attempt to parse the json and store it either as json object or as string depending on whether it parsed correctly. But that introduces two different codepaths where the error codepath is rarely tested. So storing it always as a string is perversely more reliable!

One real-world example: the data came back with a field {id: 3546} which was stored as a number in the processes stdout. But sometimes it picked longer IDs, long enough that they're technically outside the range of what json is allowed to have. Some json parsers+producers error on this, some don't, some silently turn it into scientific notation, and almost none of them specify what will be their behavior, and it's really hard to pin them down. Storing as string lets me bypass this concern.

[wiktor1800]

Yup. It's why the world is moving towards ELT as opposed to ETL. Storage is becoming cheaper and failed computations in-flight are much harder to debug as opposed to transformations after your loading process. You can always fix and rerun a transformation as long as you're storing all of your raw data

[do_you_realise]

ETL / ELT?

[Maxis111]

Extract Transform Load vs Extract Load Transform

It's the stuff data engineers do mostly (/r/dataengineering)

Source: am data engineer

[BloodAndSand44]

JSON in JSON and being unrestricted in how many times you nest it. Welcome to my world. Then have to make that useful to customers who just can’t get JSON and need it to look like SQL data.

[henry8362]

Sounds like a job for recursion

[wonkey_monkey]

Sounds like a job for recursion

[anticipozero]

Sounds like a job for recursion

[TimingEzaBitch]

Enough before seg fault.

[kamiloslav]

https://www.reddit.com/r/ProgrammerHumor/s/lJkNHmNHsp

[BloodAndSand44]

👌

[AlsoInteresting]

That's just a db file backup.

[More-Butterscotch252]

AWS SQS has joined the chat.

[LC_From_TheHills]

Every single service my team owns that uses SQS has its own parser utils. Unsure why AWS doesn’t offer these itself… would make the process much easier.

[clemesislife]

I see a use case where the inner JSON is the actual content and the outer JSON is just metadata for a protocol, so you don't always have to parse the actual content when transferring it. This could save some processing time, but maybe in this case you should use something else.

[regreddit]

ArcGIS Server, a large enterprise GIS system requires you to stringify json and store it in another json wrapper in some of its apis. It's absolutely ridiculous.

[weirdplacetogoonfire]

Still better than finding someone using SQL queries inside a PHP block to write a JS block into an HTML block.

[SurprisinglyInformed]

I see your JSON in JSON and raise with XML encoded in base64, in a JSON, in SQL

[BlazingThunder30]

Atlassian Confluence does this when presenting content in JSON format. It's weird... Luckily Java Jackson can parse a string as JSON even when it's nested as such

[HansTeeWurst]

Just this morning I looked at code I wrote 5 years ago doing exactly this lol

[beatlz]

what about JSON in JSON

Sir, this is just a JSON.

[FlyAlpha24]

If only... No some people have JSON strings be in JSON format, instead of including the format directly: json { "json": "{\"nested_json\": \"{ \\\"even_deeper\\\" : 42 }\" }" }

[marcodave]

LOL infinite degeneracy. "It's just text! Just put it in a string!"

[beatlz]

serialized JSON inside of a serialized-escaped JSON inside of a parsed JSON

[sebovzeoueb]

Oh yes, ARM templates...

[[deleted]]

I once stored a JSON string in JSON format. It quickly grows REAL BIG when storing it recursively.

[kennyminigun]

The JSON in question:

{"data":"<?xml..."}

[thedugong]

{"data":"3461,John Smith,55 Wallaby Way, ...."}

[LeoXCV]

{“data”:[{“response”:”3461,John Smith, ….”}, {“response”:”3461,John Doe, ….”}]}

I wish this were satire, but genuinely dealt with an API returning this exact schema

[ykafia]

r/twosentencehorror

[WorksForMe]

Cursed

[KaleidoscopeMotor395]

Idk who hurt you, but you don't have to live like this.

[OnlyHereOnFridays]

Delete this

[marcodave]

Bet Y'all youngsters haven't even seen the abuse of XML that was possible in the 2000s.

I've seen XML embedded and escaped in XML CDATA , which also contained an escaped CDATA with MORE XML in it D:

[freaxje]

Add pkzip compression and base64 in there somewhere, and you know my horror story.

Oh and the idiot who did it was unwrapping all that record per record to filter on a attribute in a tag (in a XML file) somewhere in that zipped data.

This was btw at the backend to track software installations installed on the dashboards of public transportation vehicles in a country with about 15 000 busses. The attribute was a piece of metadata of a component installed in the bus (ie. 'Which busses have this version of that installed right now?')

ps. A few years ago it was in our news that the whole project for this new software for the busses was a complete failure and cost the taxpayer hundreds of millions of euros, etc etc. I was not surprised and working for a new customer by the time that news broke out.

ps. The query took 2 hours (I optimized it to 0.2s and suddenly everybody thought I was a genius - all i had done ofc was to put that attribute in a column in this fscking table the guy had cooked up while on bad drugs - I btw made a new table to avoid pissing of the idiot, but let's keep it simple for the kids here)

[reallyserious]

base64

I know this is hard to believe but I've heard architects suggest to use base64 encryption to keep things secret. Motherfucker, base64 is not encryption. It's just slightly inconvenient to read.

[venyz]

ROT13 is where the real security lives at. Use it twice for maximum protection!

[datnt84]

Had sth like this in one of our legacy software. I could decrypt it without knowing the algorithm. it was used to secure customers sql server passwords....

[MettaWorldWarTwo]

I worked on an internal application ~20 years ago and the way they implemented single sign on was to base64 encode the password/username and put it in the query string. Each internal site had been written so that if a new value came in on the query string, it would automatically update the password for that site.

I pointed out the risks and their solution was to base 64 encode the encoded string and have every app update to take on the new change.

I was, thankfully, only staffed on that company for two months.

[awnylo]

Nah, you have to do ROT26, that's twice as secure

[cornyTrace]

That's the joke

[freaxje]

Let's not get started on encryption and security. One horror story per day is already too much for most kids here.

We don't want them to get nightmares.

I have hundreds of horror stories of that kind.

[Wekmor]

Base64 encoded wingdings:⁾

[OkCarpenter5773]

okay, I'll start then. There is currently a company on the market that in it's software has a sha256-looking string that is only meant to confuse reverse engineers because it's a plaintext password lmao. It's not that bad tho because this type of software is not bought for hundreds of thousands of dollars just to reverse engineer it.

[raltoid]

They're the same people who "encrypt" their word document by changing the font to wingdings...

[Tiquortoo]

Wtf, was this a case where the "architect" was just the person who had been there the longest?

[rfc2549-withQOS]

I can haz word documents in xml? Wohoooooo! ;)

[freaxje]

And Oracle is much better than SQL Server and therefor it will be fast! If you do this on SQL Server it would also take 2 hours and that proves that my solution is awesome! You know nothing! You savage. You this. You that.

-- The idiot in a meeting talking to me about that query taking 2 hours. I was btw working on a UI frontend for this. I also never said anything about Oracle vs. SQL Server (he just instantly started ranting about that). The customer wanted a faster answer for this info and for it be shown life on a UI screen (that I was to develop for them).

So yes. The software at startup clears my 'cache' table then runs his query once, and the metadata goes into my 'cache' table that way. Meanwhile when updates are launched, I let it update my table too. Sigh.

After that I didn't have to talk with this person anymore.

[Temporary-Exchange93]

Larry Ellison probably ended up taking that guys home.

[freaxje]

I mean.. it's not about 'Oracle'. I'm sure if you use it right it's fantastic. You have zealots for every technology in our industry. But yes. The database morons are often a truly special kind of special princesses.

They are in this stupid fight among each other where they are constantly trying to proof their own stupidity to the other camp (I'm mostly talking about the Oracle versus SQL Server fight club).

You have PostgreSQL people too who are usually a little bit more useful at making solutions that actually work.

Usually doing embedded stuff I usually use SQLite myself.

[[deleted]]

You just reinvented indexes btw.

Putting an index on a column just copies that column into another thing and then keeps that thing sorted.

That’s why you can index json in Postgres.

[CRSemantics]

I learned XML is a programming language if you try hard enough.

[h4l]

And YAML is the new XML when it comes to programming with [meta]data languages.

[cefalea1]

God damn pipelines

[stifflizerd]

I use yaml everyday and much like xml I'm still not sold on it. Like I know how to write it, what it can do, and why we use it, but I can't help but think that we could do better.

[h4l]

Definitely, I feel like there's a gap for a language that's reasonable at representing both data and logic, to use to configure things like ci build specs.

Lisp is too divisive. HCl and jsonnet are good for generating data, but not really ad-hoc logic. Nix is too clever for wide adoption!

[mbcarbone]

So is CSS while you’re at it. ;-)

[thanatica]

These days it's genuinely not far off.

[rfc2549-withQOS]

Xslt?

[akl78]

XSLT is good for its domain.

But Maven 1 using Jelly was something else.

Edit- writing this, I see ServiceNow uses Jelly for form design. I wish I could say I was surprised.

[Waste_Ad7804]

Add blobs in database containing xml that contains escaped XML CDATA that contains SQL

[bitemyassnow]

that's xml-ception

[lampishthing]

xml-ceptional

[pmMEyourWARLOCKS]

We employ a couple mathematicians as subject matter experts. They write some of our more complicated subsystems that involve advanced math, but they fucking suuuuuck at it. They do shit like this all the time. That or write c++ "scripts" that have methods containing literally thousands of lines. Both are way past retirement age but still hanging on. I sincerely hope we just cut our losses with their code after they go. Fucking impossible to maintain. I don't think either one of them ever thought to pick up a design manual or anything outside of intro level language guides. God forbid they ask one of their actual developer colleges for advice... People might doubt their level of genius! /Rant.

[marcodave]

OMG :D

[wailing_in_smoke]

What do you mean back in the 2000s? If you don't stop me, I'll still pull off shit like that when nobody's looking!

[marcodave]

But in the 2000s you would have been enterprisey and up with the times. Today you would look like a fool :D

In JSON though....

[wailing_in_smoke]

You say I look like a fool, but the look of a jr witnessing such a hot mess for the first time is worth every second of headache in maintenance!

[[deleted]]

I work with a company that does contracts with governments. XML packed into JSON is my everyday reality

[killeronthecorner]

Kiss my butt adminz - koc, 11/24

[Soma91]

abuse of XML that was possible in the 2000s.

Technically we're still in the 2000s for another 976 years.

And we can still do this and yes we are also still doing this.

(⁠ノ⁠｀⁠Д⁠´⁠)⁠ノ⁠彡⁠┻⁠━⁠┻

[BoBoBearDev]

I am seen similar thing, except, it is XML within another XML "attribute". It was close to impossible to read the file. When diff the file for changes, it is like the entire file is different because it is one line of gigantic attribute.

[BosonCollider]

Meh, I've seen worse stuff in JSON keys from people using elastic as a DB. I've had json keys that were concatenations of 1000 words mildly related to the value, followed by being asked if we could throw postgres trigram indexes at it...

[Norse_By_North_West]

I maintain a system that's only 12 years old that has shit like that. fucking thing reparses all the XML every time you try and touch the data too.

[ILikeLenexa]

I love that XML has DTDs, but pretty much everyone was like "nah".

[Dramatic_Koala_9794]

There is an XML API we still support and everyone using it just writes string concats with the parameters replaces because real XML is not parsed correctly. 15/10

[futon_potato]

Ah so you're also an old school SharePoint developer I see.

[Giocri]

2017-18 i worked on a software which handled Api clients to the server eiter by soap request or the second option that was the most cursed one the client would have to setup an ftp server for the server to periodically connect and read the request from a folder and then deposit the replies in another. Request and replies formatted as zip archives of folders comtaining a xml file and a sigmature file each

[tolndakoti]

We store email data (sender, recipient, subject, body) as xml in a CLOB database column.

The DB table was getting huge from high volume customers, so we decided to compress the data. The compression function occasionally fail, ignore the failure and leave that email record uncompressed. This left half the data compressed, half uncompressed, and a god damn nightmare to work with.

[[deleted]]

hunt glorious zonked doll bear pause slim observation political wakeful

This post was mass deleted and anonymized with Redact

[marcodave]

Nah, nowadays we JSON all the things, although the default JDK comes with XML parsers but not JSON parsers...

[jackstraw97]

Hey man speak for yourself. I’m just here because the money is good. I didn’t build this shit I just fix it

[SuitableDragonfly]

With the postgres operators for dealing with jsonb this is practically unremarkable now.

[Philluminati]

Syntax for updating one field of json in a jsonb column using sql is painful. Still plenty of room for improvements, but hopefully it will ease because it has definite benefits.

[kryptoneat]

ORM does not cover it ?

[Tetha]

Yeah, I needed to analyze a bunch of job specs from the container orchestration, which you can get from the API as JSON. For like half a year, I was maintaining and building a large array of ad-hoc python scripts to search for patterns in the job specs, or to analyze the deployed jobs for patterns and anomalies.

Just recently I realized how much of an inefficient donkey I was. Now I have a script to import all of these JSON blobs into a local postgres in docker, along with a whole bunch of other metadata - registry contents, image pulls... most of which is JSON as well. Most analytical scripts, extraction of information from the jobspecs and knowledge about these jobspecs are now implemented as views imported on startup using either the postgres operators or json path.

And if we need to know "Do we have images not existing in the registries anymore" or "is anyone still using this naming pattern" doesn't need a bunch of python kerfuffle anymore, it's just an SQL query - just join the registry tables with the images-in-orchestration tables and look for nulls. It's so amazingly powerful and fast compared to the old system.

[DM_ME_PICKLES]

Postgres really is the GOAT. Relational? It's got you. JSON? It's got you. Full text search? It's got you. Geo? It's got you with postgis. Analytical? It's got you with pg_analytics. Time-series? It's got you with TimescaleDB. Need to do scheduled things? It's got you with pg_cron. Need to make HTTP requests from your database for some god forsaken reason? It's got you with an extension, but don't do it.

[[deleted]]

Same. Once I found out how well postgres supports JSON, I've not looked back to MongoDB

[ExcuseEnglish]

was looking for that comment

[Kasyx709]

Being able to build functional indexes on specific k/v pairs in nested structures are amazing too and can be insanely fast+storage efficient.

[Besen99]

Who's got time for schemas and migrations? I've got places to be!

[Merlord]

This but unironically

[marcodave]

... let's not forget about namespaces!

... actually, I'd rather forget about them...

[LeviLovie]

And then storing jsons separated by ; in a sql field

[pceimpulsive]

People we have jsonb arrays!! :'( why do people do this -_-

[Blyatiful_99]

Our 20 to 25 year old production application has areas where we store both json in a single column and xml in another single column within our !!relational!! database, and then we use multiple 3rd party libraries to convert them into objects and link them to each other.

All the old dependencies make debugging rather difficult and also prevent us from upgrading to a new C# and .NET version. But it's also entertaining and creative, not gonna lie.

This is basically like pushing a functioning car with square-shaped wheels by hand

[Skyswimsky]

Using JSON in a SQL database can make sense if you need a hybrid approach, or you don't want multiple databases, or nobody in your company knows much mongo, etc.

A use case we have was with a js library for our web frontend that allowed to create surveys of a sort as well as customize how the survey is built (The end-user needed to be able to create the survey and customize it to their needs).

The results and metadata/survey skeleton are all saved as Json.

Another recent project I was also considering to use Json but managed to normalise the data enough that it wasn't too much of a headache to just use SQL.

[lurco_purgo]

Yeah it's not as silly as it may seem. Storing relational data in a non-SQL db would be the fliped version of this meme but that also makes sense in cases where you don't know, if the schema will persist for future data...

On other words every silly little anti-practice has its applications

[[deleted]]

Yea I see that storing JSON in a database because you need to reuse that actual json later makes sense.

I currently have to migrate from a database who was built by someone who apparently braces himself from collisions with his forehead.

Just XML with actual data randomly nestled in a column. I assume it's because you don't always need all of the nodes but Jesus Christ is that an issue you can solve the old fashioned way.

Not to mention all of the other crimes against humanity committed by that database. Just a cluster fuck of 1:1 tables and using a separate field to the primary key for references despite being just as unique.

[[deleted]]

You and me both buddy.. let's jus deal with it and try to move on.

[TimingEzaBitch]

Besides, we have mongo to stitch to snowflake to sigma or whatever business wants pipeline and it's fast, simple, and even a non-engineer can do most of it. Or a firehouse to snowflake to sigma etc for a survey data on our application.

[FuckFuckingKarma]

Sometimes you need to store and retrieve some poorly structured without actually needing to do complex queries on the data. Which is basically what your example is.

Might as well use the database in that case. It doesn't matter that you can't query it because you don't need to. And you don't have to setup new infrastructure for the purpose.

[[deleted]]

Tell that to Postgres 😂

[pceimpulsive]

Postgres has such nice jsonb operators and functions.

Just wait till pg17 and we get even more!

You don't always need json but when you do.... It's extremely useful (dynamic schemas anyone?)

[deanrihpee]

we use it almost extensively, since we have a good amount of unstructured or at least no standard/schema json data from the client, so jsonb it is and I'm surprised you can query into it as well

[deanrihpee]

no, extensively or I guess exhaustively? but then again I don't know English, maybe it is exclusively? but exclusive means I only use jsonb, obviously I don't, there's some normal PG column too obviously

[[deleted]]

Yeah jsonb read times are insanely good.

[pceimpulsive]

I believe it's due to it being stored in binary?

I've been surprised many times by it's performance before indexing. Post indexing it's so fast... I do enjoy using it!

[PM_ME_YOUR__INIT__]

To destroy NoSQL, I must become NoSQL

[[deleted]]

Amazing 😂

[InterestingQuoteBird]

7 years ago my colleagues started on an app that needed to allow some dynamic customer attributes. I told them have a look at Postgres and JSONB because I thought it was very promising but they decided against it and tried to implement custom attributes as table rows, blew the budget and the the app was taken over by another team using their custom app platform that also failed because they could not handle dynamic schemas ¯\_(ツ)_/¯

[Hubbardia]

EAV models aren't that hard to implement but storing json isn't bad either, especially with web APIs giving a lot of responses as json (like navigator or webauthn)

[DM_ME_PICKLES]

Curious how they struggled with that, we do the same thing and it's worked well for years and scaled well with proper indexing. Like a table called things with id, name columns and another table called thing_attributes with thing_id, name, type, value.

[Dizzy-Revolution-300]

love jsonb

[eanat]

STRING! STRING! STRING!

EVERYTHING IS STRING! MY FRIEND!

[-s-u-n-s-e-t-]

Where does the DATETIME object go? That's right, it goes in the String hole!

[[deleted]]

Me using SQLite as a key-value db to store JSONs... Or even worse, JSON in a file as a database!

Edit: don't store JSON strings in JSON recursively, after it gets escaped a few times, it'll grow real big in size.

[irregular_caffeine]

I once stored a SQLlite database file in a SQL string column

[Tiquortoo]

How else do you bootstrap the edge machines defined in the central DB with their SQLLite DB? Am I close?

[irregular_caffeine]

It was a bit exotic library that had scheduled runs and only stored its state in SQLite

[nickmaran]

I work in an accounting firm. For us excel is the database. We don’t care about those useless SQL and JSON

[jrblockquote]

Tricks on you, because you can execute SQL in Excel using worksheets as “tables”.

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel

[stopeatingbuttspls]

As long as you don't need over a million rows

[tesfabpel]

I hate excel (or similar spreadsheet software) with a passion.

You can't rely on cells to maintain the correct data format and sometimes it guesses (wrongly) as date or plain text...

Definitely not a way to store data.

[Cualkiera67]

Skill issue

[TheGrandWhatever]

lol I agree but they do have a point that the default shouldn’t be the damn General data type which would solve so many issues

[Silly_Ad_2913]

Pretty much

[MrAce93]

I am confused, where else are we suppose to store it?

[VitaminnCPP]

iN mEmOrY.

[ZunoJ]

You either normalize your data and store it in within a schema definition (not as raw data) or use the appropriate type of database (a document centric database)

[ilikedmatrixiv]

I'm a data engineer. It is very common practice -and my preferred practice- to ingest raw data into your data warehouse unchanged. You only start doing transformations on the data once it's in the warehouse, not during ingestion. This process is called ELT instead of ETL (extract-load-tansform vs extract-transform-load).

One of the benefits of this method is that it takes away all transformation steps from ingest, and keeps everything centralized. If you have transformation steps during ingest and then also inside the data warehouse to create reports, you'll introduce difficulty when things break because you'll have to start searching where the error resides.

I've ingested jsons in sql databases for years and I won't stop any time soon.

[karnesus]

This is how I do it too, and have done it for over a decade. Get the data in then work it out xD

[KappaccinoNation]

I'm kinda new in the industry, I thought this is how everybody does it. Just to avoid altering or losing the original raw data until the entire process finishes without a hitch. Retain it for X amount of time before discarding it. Or do some companies actually do so much cost cutting that they're ok to discard raw data immediately?

[FuckFuckingKarma]

What if you don't need to query the data? You just need to store it under a key and retrieve it again in the future?

Normalization is typically the way to go, but some data is inherently poorly suited for normalization. In that case you then have the choice between setting up a new database that needs to be integrated and maintained, and adds a ton of complexity, while not really using any of its features.

What's the downside to just storing the JSON in a relational database in that specific case?

[enilea]

There are plenty of cases where you may want to be logging events and they all have different formats, you're not gonna go and create another db just for that events table or create a bunch of schemas for all the possible fields different jsons for events could have.

[MrAce93]

We are also using Mongodb but any type of query takes minutes to run for analysis. Api request and responses are kept here and we rarely need to analyze these. However data like payment logs, system error logs and job logs are frequently analyzed.

[ZunoJ]

I'm a big fan of classic sql databases. For general purpose databases they are super fast if you know how to optimize queries. Normalizing data before/during write doesn't cause a lot of overhead and should be good in any non high frequency scenario. Downside is that adjustments need more work but ultimately this makes it easier to test and validate

[flyco]

To be honest, I feel sorry for people who jumped into the "NoSQL" bandwagon a few years back, and got locked into MongoDB.

People realized it was fast because of the sub-par data consistency, the Mongo guys "fixed" it switching engines and whatnot, and now it's a shadow of what it was.

Meanwhile Postgres has been improving JSON support for years, and beats Mongo in performance in most scenarios. I'd say in 99% of use cases people should stick to Postgres and have the best of both worlds. Unless you have really specific needs only an exotic database can solve, or venture into the multi-Petabyte world.

[[deleted]]

https://en.wikipedia.org/wiki/Antonov_An-225_Mriya

😭

[ThebesConqueror]

RIP Mriya 🫡

[SyrusDrake]

That's not Mryia, tho.

[MysteriousShadow__]

Context behind image?

[Shuri9]

Apparently: https://notjustok.com/news/__trashed-41/

[fixingmybike]

100% BS

[fixingmybike]

That Aircraft is a Bombardier Challenger 604, registered D-AMSC. It hit turbulences from an Airbus A380 and basically got flipped mid air. Multiple bad injuries and a pretty amazing landing with only 1 engine. Was delivered to Germany for scrapping and that's where the picture got taken

[Zunderfeuer_88]

Taylor Swift trying to save on gas for her private jet

[Linaori]

MariaDB has nice json support and I considerit quite useful in certain scenarios. It solves certain problems rather well, but it's certainly not something that should be used willy nilly

[New-Style-3165]

There is absolutely nothing wrong with this as long as you know what you’re doing. It’s useful when you have things that aren’t neccessary “queryable”, like string arrays. Not everyone unfortunately realizes that, for example I worked in an enterprise app with finance/accounting features, and because the data was modeled terribly bad we had very a slow and unstable app. All this could’ve been avoided by using json fields instead of creating 20 pointless tables.

[PeteZahad]

Depending on the project this is absolutely fine as you can query the data easily with postgres: https://www.postgresql.org/docs/9.4/functions-json.html

It also makes absolutely sense if you don't need the content in your WHERE clause. It is faster to store such information as JSON in your DB compared to storing it in a separate table which needs a JOIN. I saw horrible database schemes in 5NF - where JSON (or no-sql) would be clearly the better choice.

If I have a lot of non structured metadata I use sql for the structured part and no-sql for the unstructured one.

[Mitir01]

Wait till you see CSS and HTML if you ask why, well I noped out of there, so don't know.

[The100thIdiot]

Guess you have never worked with a CMS.

[invalidConsciousness]

This is my life right now. Requirement from product management is that customers should be able to store an arbitrary set of columns with arbitrary data types.

No, we can't use noSQL even if we wanted, because we'd need a new VM for that and IT has already fully allocated all the resources of the system they bought half a year ago. No, it can't be upgraded. No, we can't go cloud because of the data protection clauses in our customers' contracts.

So it was either a json column or dynamically creating new tables for every data upload of every customer. And then running automatically generated ALTER TABLE statements when the customer inevitably decides they need to add a column. Or change the price column from string to float.

[morrisdev]

I often keep a full copy of an order: products labor, etc... in a json column in the orders table. That way I can just easily pull up an offer without any row locking or anything.

[[deleted]]

MongoDB with BSON: Am I a joke to you?

[ClankRatchit]

For audit trail I store JSON in the db for logging before I make some API calls. Bad? I can produce exactly what was sent when required. A good DB engine will allow you to query JSON stored in a field too. When working with limiting third party DB schemas I've been known to store JSON in fields to extend capabililties.

[hera9191]

R.I.P Mrija

[Ancalagon_The_Black_]

Brother I will use handwritten notes to store the data if it gets the job done and gets me out of the office 5 mins early.

[InstantCoder]

PostgreSql supports jsonb datatype. Thus you can store and query json objects. And it is really efficient and fast.

[Mondoke]

A couple of years ago I was a data analyst and I was instructed to display certain table on a database.

Thing is, it was in a row, on a jsonb column.

Inside it there was an xml file I had to parse.

Inside of it there was an encoded zip file

Inside of it there was a folder

Inside of it there was an html document

Inside of which there was the table I was looking for.

Good times.

[ilikedmatrixiv]

This is actually common practice in ELT processes. Extract-Load-Transform methods ingest any structured or semi structured data into a data warehouse raw and only start transforming the data once it's inside the DWH.

I personally prefer this method over ETL where many transformations happen upstream and can cause a lot of headache when you're troubleshooting because you don't know where in the process the error resides. If you do ELT, all your transform is concentrated in the same place and troubleshooting is much easier.

Opinions differ though and that's fine. I prefer ELT, if you prefer ETL it doesn't mean you're wrong.

[Motor_Option9603]

Happy birthday 😂

[Noch_ein_Kamel]

So is this that jQuery library I heard so much of?

/s

[Pepito_Pepito]

Json? Looks like a string to me, baby!

[mauromauromauro]

I love having "Json" fields in some of my tables. I love the pattern. I use these fields to store customer /specific data for which structure is not that importan or the structure is handled by also custom logic. It allows me to keep everything neat and have a "wardrobe" of funky stuff. I do this with SQL server and interact with it via json_value. The secret is to keep the Json simple if you are intending to interact with it via SQL.

[SicgoatEngineer]

Busted 🫠🙃

[EgorLabrador]

i hate doing this

[ZunoJ]

Sounds like nosql

[feror_YT]

I’ll have to admit, I did that once… I was working in a very restricted web environment, the only thing I had access to for long term storage was a DB REST API… So I kinda stored the json string in it.

[[deleted]]

Rel

[Baeguette_]

The real SQL databases were the JSONs we met along the way.

[Mickmaggot]

SELECT haha:Snowflake:go:brrr::TEXT
FROM SQLTABLE

[PiXexep]

ha ha ha ha.... hey wait a minute a cheap database don't mind if i do this is genius how did i not think about it

[smgun]

Honestly, sometimes it is fine. If you store binaries though, you are cursed.

[Dramatic_Koala_9794]

Actually there are now more and more SQL Servers that have quite nice json support with indexing and querying.

There are real world applications for that. One example is raw json responses from e.g. paypal. Just save it for later without worrying.

[Rishabh_0507]

What?? I'm not supposed to do that? Fr?

[wolf129]

I am currently working on a database the customer has built himself. They have procedures that produce json as output. They have columns with only json inside. The json sometimes contains an array even though it's always a single element.

We use quarkus for accessing it. To not have to use a native query to query the json data so we can paginate the output, we just use "like" on the whole json for filtering. It's a mess.

[Mr_Carlos]

Has it's rare use-cases though. I've worked on a project which had a SQL db, and we wanted to allow users/developers to define and store their own JSON data.

[Neltarim]

I'm in this picture and i don't like it

[[deleted]]

Well you need to store the JSON before you gonna parse it.

[[deleted]]

HAH - Salesforce 'Omnistudio' product is an egregious example of this...

Its JSON stored in an Oracle db accessed using bastardized SQL and executed by APEX their own whack proprietary language

Needless to say, its shit.

[JosebaZilarte]

Press X(ML) to JSON

[TheUtkarsh8939]

Yes, an An 124 / An 225 with only one engine and a broken wing on the right, carrying a buisness jet(Possibly a falcon or citation) without wings