That's because the column name is part of the RDBMS' structure, as is the type, for the most part. If you want to read a column and use the row values as the column names, that can be done, it's just a query. The DBMS is going to have to scan the whole table (or lean on the index) for that column anyway. I can't think of why I'd want the RDBMS to do that for me and imply the column names, rather than me implement it in code. I'm managing the column names as data at that point. That's if you still think it's a good idea.
The other piece that jumps out at me is that when you do something like that, you're really building a variably wide table, but not letting the DBMS manage it as a wide table. Instead, every query is going to require either you or the DBMS to transpose rows to columns at some point in the query - if it's a simple select, only on the return, which won't be that expensive, but some joins are going to require it to happen earlier. I'm not smart enough to know whether it'll be computationally expensive to the point of being a showstopper, but I do know it'll require a lot of memory if you want it done fast, or you have to do it slow in scratch/spool.
That don't seems like rocket science to me... duh...
PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints.
There are other differences, both advantages and disadvantages to doing this with Postgres (which is the only db with a type called 'jsonb' I think?), vs some kind of non-SQL document store. I agree that as Postgres json(b) gets better and better, the disadvantages decrease. And one of the main advantages to me is simply not having to have another system to run and understand, since postgres is probably already there.
But it's always trade-offs and choices, that you make better with more experience and better understanding of your domain. It's definitely not a "duh, it's always obvious" thing. Unless your domain is so simple that it doesn't hardly matter.
Or anything that you might want to use as a foreign key down the road. It requires you to know that now, or modify later, so we might as well declare a schema and manage it unless we're totally certain we won't need any additional keys. But if we're certain of that, we could just use a schema.
I'm currently implementing a mixed solution where clearly defined properties (and thus ID) have columns and constraints but I also include a schema-less "all you can eat" jsonb field so that experimental properties can be stored.
Upon each release I will migrate validated shema-less properties to static columns or even sub-tables. Similarly if a column is remove on next iteration I might transfer attributes to the jsonb to revert it later if needed. This does include a migration step but this seems only like good practice. Of course scaling... but I'm really not limited by this now.
The client will interact with the model through views or functions so that he don't have to care if columns are real or not. But anyway as soon as PK/FK are needed I will probably straight-out think that jsonb will be insufficient and take 5 minutes to think of a relational way to implement.
I don't have a lot of experience with it, but Postgres supports indexing on jsonb fields. If you do end up in a situation where your keys are in the right place and you have some data in a jsonb, you may still be able to get at it efficiently.
Part of this is how we approach development as well. For one, I'm now some management type that makes powerpoints, so any code I write is on my own projects. But when I did more of this, my approach was always to start with an RDBMS unless I had a pretty specific use case to not use one. Having done it long enough, I am being honest that I find maintaining a database schema, migrating changes, and all of that to be pretty trivial. That's my approach, but I don't see an issue with yours if you think it's making you more efficient.
I should ask, because I'm assuming it, but make sure whatever you're stuffing into the jsonb field is true row-level data. Try to avoid putting data in there that "belongs" on another row - at the extreme it could provide some unintended exposure. Hypothetically, a row containing Customer A's demographics in the "person" table has a jsonb field - it may be tempting to put some basic demos about other customers B, C and D connected to that customer A in some way, but that's data you'd not want to expose as belonging to Customer A. Yet, if it comes back on the same row, it's as trivial to leak it as it is to retrieve it.
EDIT: To actually answer your question, I don't see how schema-less can have foreign constraints. Foreign constraints are defined in the schema. What I was saying is that if you're going to need joins later, have enough schema to support that.
I've only start to explore all possibilities, but main use so far:
-Store experimental properties before next schema revision
-Particularly good alternative to composite-type in early stage dev if you want to make live modifications without replaying the whole deployement script. (Composite type usually have huge cascading constraints)
-Storing "metadata row" for later use
PS: Actually it may even be an alternative to composite type in production because I can totally picture case when you need row level "objects" that vary depending on the value of another column. Yes you can normalize, but it might be more maintainable to keep everything in one table an write an appropriate check constraint. And it will still be easily human readable as opposed to a literal representation of a custom type or whatever (think of PostGIS geometry type that is very versatile but totally opaque to humans).
In MySQL 5.7 you can have tables with JSON columns (like PostgreSQL's JSONB), and also "Generated columns" which are expressions over other columns (e.g. extract a value from the JSON). You can then use those generated columns as the source or destination of foreign key constraints.
On those "generated columns" you can create indexes, reference other tables (foreign keys) or be referenced by other tables (foreign keys).
MySQL has a binary JSON data type (like most databases) and also expressions to extract data from those JSON fields.
MySQL has a lot of disadvantages vs PostgreSQL, but having foreign keys on elements within JSON (via this mechanism) is a thing MySQL can do that PostgreSQL can't :) (Unless I'm mistaken?)
However on a solution I've implemented on PostgreSQL I expand jsonb as regular field in a view and made that view editable via trigger. This way users can update jsonb_field without being aware of because they edit it through a view as if they are normal fields.
It is fundamental to the relational model that metadata like that is stored in and queryable from relations just like normal data.
I also think so but the issue has several important aspects which justify the use of the separate term "schemaless". One of them is that schema elements (say, column names) can be stored as normal data. For example, instead of having normal columns like Name, Age, Department, we could introduce a column storing these strings in 3 rows. As a result, DBMS is simply unaware of the schema - the schema exists only in our head (and in the app). As a consequence, DBMS cannot help us too much in managing data, and instead our app becomes responsible for these tasks, hence we get problems you mentioned. But the major problem is that currently there is no technology that allows us to say that this table column stores actually column names which can be used in queries and have to be treated as normal columns.