You can store this just as any other exists only content. If you do not want to use data, which is stored within your JSON for expensive operations (filter/join/sort).There are some workarounds (depending on the RDBMS you are using), but most of them don't work the way you'd like it. that such operations will shift quite some extra load onto poor little DB server.that a JSON is not easy to read, and it is very hard to filter/search/join or sort by it.that a JSON might change in time (same column, differing content).
that a JSON is quite probably breaking 1.NF.Your lovable DB artist :-D knows, that storing JSON as is, is against common principles of RDBMs. Any need to read a value out of JSON will need a hell of lot of hidden string method calls.
Sql server xml query vs json query update#
This might come later, but for now I'd assume, that JSON will not be as performant as XML on SQL Server (see section UPDATE 2). There is no real native JSON type (like there is a native XML type). JSON in SQL Server (2016+) lives in a string and must be parsed.
Querying this is astonishingly well performing! This structure is not parsed on string level! XML is not stored as the text you see, but as a hierarchy tree. The following is for SQL Server and might be different on other RDBMs. If this data is a unique portion, it's okay.īut if you need the internal parts regularly or if this would mean redundant duplicate storage it's not okay. Some days later the person tells you a new address? No problem! The old address lives in an XML/JSON if you ever need it.Ĭonclusion: If you store the data just to keep it, it's okay. You want to persist the person's data for a given moment in time. Is this wrong? Shouldn't this rather live in properly designed related tables with a foreign key reference instead of the XML/JSON? Especially if the same person might occur in many different rows it is definitely wrong to use an XML/JSON approach.īut now imagine the need to store historical data. Now you put this into one column as PersonInCharge. Now imagine a complex structure (XML or JSON) representing a person (with its address, further details.). You get a SOAP response and want to store it because you might need this for future reference (but you will not use this data for your own processes)? Just store it as is! It is perfectly okay to store a complete structure as one bit of information if it is one bit of information actually. What about XML or JSON? Are these types breaking 1.NF? Well, yes and no. You see a column "PersonName" with a value like "Mickey Mouse"? You point to this and cry: Change that immediately! The first rule of normalisation dictates, never to store more than one bit of information into one column. In most cases it will be better to transfer your JSON data into normal tables and re-create the JSON when you need it.
JSON (like XML) is great for data exchange, small storage and generically defined structures, but it cannot participate in typical actions you run within your RDBMS. How are you filtering/sorting/joining/manipulating this data?.What are you going to do with this data? and.
0 kommentar(er)
