IT:AD:SQL Server:HowTo:Work With JSON
Summary
In a word – it's doable. But don't.
Notes
JSON is currently one of the most commonly used data exchange formats. It optimized for portability, space, and legibility. Not for indexing and retrieval speed.
ACiD: * Advantages:
- Provides hierarchy to key/value pairs.
* Considerations:
- Format is used for document databases.
* Disadvantages:
- Combines multiple entities. Blurs and obfuscates conceptual boundaries, leading to flawed analysis.
- Not portable to other databases.
- Not natively available via ORMs.
- Breaks stated Design principles of Application logic transparently handling objects versus handling the serialization thereof (SQL).
- Increases testing requirements considerably (unmockable).
Valid use cases: * Staging table for JSON imported from remote systems. * Document Store…but then why not use a document database for just that?