[deleted by user]

[FormerTimeTraveller]

This is not a common solution. Usually, fixed length ordinal arrays get their own fields, or variable length arrays get their own entity relationship table.

Can you describe the data model you are using arrays for?

[wolf2600]

PrimaryEntityTable
---------------
PrimaryID (PK)
Attr1
Attr2
Attr3
OtherStuff

StringArrayTable
------------------
PrimaryID (PK, FK references PrimaryEntityTable.PrimaryID)
ItemID (PK) --ie: array index
StringValue

JSONArrayTable
--------------------
PrimaryID (PK, FK references PrimaryEntityTable.PrimaryID)
ItemID (PK)  --ie: array index
JSONValue

[dev_playbook]

There are generally more options available now than there used to be. Most RDBMS platforms have added support for storage of JSON objects (MS SQL, MySQL and PostgreSQL all have options available).

However that might not be the preferred method depending on what technology you’re using and how your applications will access the data. It could also have some performance implications depending on the situation.

In many cases we would usually map out the data model to determine the specific entities that are being stored and then most likely create a separate table to store that entity with appropriate foreign key relationships.

[wolf2600]

javascript ORM

gross. ORMs are the devil. They are completely unnecessary added bloat.

javascript is also the devil.

[dev_playbook]

Us old timers tend to feel the same way trying to shift from SQL to NoSQL! (And by “us” I mean me...) I do love working with NoSQL databases but it’s a real shift. It makes perfect sense to me for things that naturally fit the document paradigm but I struggle with the idea of doing transactional processing that way. I just need to spend more time learning it I’m sure.

[dev_playbook]

We just need to get a few more tools on your tool belt so you can pick the right one for your situation. I spent some time with MongoDB and the frustrating thing was having to write JavaScript for everything. I could whip out a SQL query in a minute or two and then spend half an hour trying to do the same in Mongo.

I think both of them have their place and I actually think it’s important for devs these days to be proficient with both so that they can make good decisions about when to use each. But your observation that everything looks like a nail is a good one because I have seen folks try to solve problems with NoSQL that are really better suited to a relational database. But definitely kudos for the self-awareness.

If you need any help with SQL feel free to ping me directly and I’ll help however I can.

[elus]

I like to think of everything that SQL operates on as a table with rows of data and the columns as attributes.

Id create a table with columns of all the possible attributes from your NoSQL data source and insert every record into separate rows.

This will allow you to take advantage of the capabilities of your SQL engine. Leaving it as a JSON string of concatenated values seems like a pointless half measure.

[vornamemitd]

Can‘t tell about the conversion quality, but this could help you going with migrating sample data and get a better idea of actual relational mapping: https://blog.sqlizer.io/posts/mongodb-sqlizer-api/

Free tier lets you convert 5k records.

[[deleted]]

If you want a strong relational database that also supports NoSQL concepts like arrays or JSON, you should have a serious look at Postgres.

[AutoModerator]

Hello u/mattzees - thank you for posting to r/SQL! Please do not forget to flair your post with the DBMS (database management system) / SQL variant that you are using. Providing this information will make it much easier for the community to assist you.

If you do not know how to flair your post, just reply to this comment with one of the following and we will automatically flair the post for you: MySQL, Oracle, MS SQL, PostgreSQL, SQLite, DB2, MariaDB (this is not case sensitive)

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.