Json

JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS

• Updating a row in Postgres always writes a new version of the whole row. That's the basic principle of Postgres' MVCC model. From a performance perspective, it hardly matters whether you change a single piece of data inside a JSON object or all of it: a new version of the row has to be written.
• Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently.

Working with JSON

Functions

imagine we pass a serialized json object as an argument to a postgres function like so:

{
	username,
	avatar_url,
	email,
	first_name
}

We can then unpack that arg into a declared postgres variable:

declare
	v_email = profile_object ->> 'email';
-- imagine this as `email = profile_object.email`
-- `v_email :=` syntax is identical

-> vs ->>

-> Returns the field as JSON

• because of this, we must use this for chaining to access deeply nested fields:

SELECT info -> 'items' ->> 'product' as product

->> Returns the field as text

Contains (@>)

We can check the JSON to see if it contains certain key-value pairs:

select media_items
from nuggets
where media_items @> '{ "type": "text" }'

JSON vs JSONB

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

JSON

• JSON - stores an exact copy of the input text, which processing functions must reparse on each execution
  • Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects
• JSON values can be manipulated, but must be cast to text first.

JSONB

• JSONB - stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed
  • also supports indexing on GIN and GIST index types.
  • does not preserve the order of object keys
• gives us capability to query into our JSON document for quick lookups

---

E Resources

https://www.postgresqltutorial.com/postgresql-json/

UE Resources

PG plugin to query jsonb data

---

Children

1. Cook