A document in a cell: object, array, value

Chapter 1 — “JSON in a cell”

The Sorting Room is behind the bulkhead now. The compartment next to it, on the station’s Intake Level, is called the Unpacking Bay: racks run along the walls, and parcels sit on the shelves. “Parcels” is what people here call the second part of the first signal. Along with the Kotomarket database and the raw text came the store machines’ internal correspondence for the last two weeks before the cutoff, February 10 to 24, 2025.

The machines were chatty. The mobile app reported every tap on the screen: viewed a product, added it to the cart, placed an order. The payment gateway sent the store long replies. Product cards stored specifications that never got columns of their own, and each customer’s settings sat in a separate document. And curly braces showed up almost everywhere.

The Commandant sets the task in two lines. He needs a summary of the store’s last two weeks: what people bought, how many orders there were, how they paid.

You open the first parcel: event 3 from the app_events table. Four columns, nothing unusual at first glance: an ID, a timestamp, the type purchase. But the fourth one, payload, has an entire order packed into it. There’s the customer and their city, two items with prices, an empty list of coupons, and a total. To the table, that’s a single cell. Inside it is a whole tree.

QUERY: Picture a table as a cabinet with shelves, and JSON as a box with more boxes inside. For now the job is simple: figure out what sits where, and what gets lost if you repack the contents.

We won’t pull values out one by one yet. We’ll take a single cell like this and work out how it’s built: where the objects are, where the arrays are, what type each individual value has, and what exactly PostgreSQL does to the original text when it turns it into a document.

What a document is made of

Let’s take a simpler event. Event 1 is a view of the headphones, and its payload fits on one line. This is how PostgreSQL prints it:

{"app": "4.6.0", "user": {"id": 23, "city": "Санкт-Петербург"}, "price": 5990, "product_id": 1, "schema_version": 2}

Curly braces enclose an object. Inside, “key: value” pairs are separated by commas. A key is always a string in double quotes: "app", "price", "product_id". The values, though, vary. "4.6.0" is a string, so it’s in double quotes too. 5990 has no quotes: it’s a number. The value of the "user" key is more complex: another object, with the keys id and city. A box inside a box.

Now back to the order from the scene, event 3. It has an "items" key, and its value starts with a square bracket: [{...}, {...}]. Square brackets mark an array: an ordered, comma-separated list of values. Array elements have no keys, but they do have an order: first, second, and so on. In order 5001, the items array holds two item objects, one for the headphones and one for the T-shirts. Right next to it is "coupons": []. That’s another array, just an empty one: the order had no coupons.

A value with no nested objects or arrays is called a scalar. JSON has four kinds:

• a string in double quotes: "Санкт-Петербург" (St. Petersburg);
• a number without quotes: 5990, 1.2, -3;
• a boolean, true or false (the wireless headphones’ card has "wireless": true);
• null, the value that means “nothing.”

There are no other basic kinds. JSON has no dedicated date type, for instance: a time has to be written as a string or as a number of seconds. Single quotes aren’t allowed, and neither are comments. From there, these few pieces can be nested inside one another to any depth.

JSON stands for JavaScript Object Notation. The notation grew out of the way JavaScript writes objects, and in the early 2000s Douglas Crockford described it as a standalone data-interchange format. These days JSON arrives constantly from web services, so finding it in a database is more the rule than the exception.

Where JSON turns up in real work

The parcels came from five places. Sources like these turn up constantly in product databases.

app_events.payload — events from the mobile app. The set of properties depends on the event, so developers don’t create a separate column for every field. A view needs only a product and a price, while placing an order also needs items, coupons, and a total.

payments.response — responses from the payment gateway. That’s someone else’s API, and the store has no control over which fields will show up there tomorrow. So it’s convenient to save the entire response exactly as the gateway sent it, then parse out the fields you need later.

product_cards.attrs — product specifications. A book has a page count and a year of publication, headphones have a battery and colors, a coffee maker has a power rating. If you give every specification its own column, the table sprawls fast, so properties like these often go into a document.

user_settings.settings — customer settings: color theme, newsletter, notifications, experiment flags. When the app adds a new setting, one more key appears, with no table migration.

raw_inbox.body — the incoming stream for the final evening: webhooks, imports, messages from partners and from the support bot. This one is a genuine crate of raw material: input is written here with no validation at all.

In other people’s projects, I’ve run into JSON most often in the replies of external APIs. A team saves the whole response “just in case,” and half a year later it suddenly turns out that some rare field in it was needed after all.

The cell below takes one document from each table and shows the type of the column that stores it. The documents are cast to text, so all five rows fit into a single doc column and PostgreSQL shows them as text. Before you run it, make a prediction: in four of the tables, the document column has the type jsonb. So what will we see for raw_inbox.body, where data is written without any checks?

The document as a staircase: jsonb_pretty

The payment row in the previous output stretches far off to the right. With text like that, you quickly lose track of where a nested object ends and which level the next key belongs to.

For this, PostgreSQL has jsonb_pretty. The function prints a document as a staircase: each key goes on its own line, and each level of nesting is shifted right by four spaces. It takes a jsonb as input and returns ordinary text.

Take the order from the scene. Before you run it, use the diagram above to count how deep the longest branch goes: the outer object, then items, then one item. And while you’re at it, try to guess what the empty coupons array will look like in a staircase like this.

Open the next document without the diagram. Take payment 2, the decline from the overview, and before you run it, find three things: what nesting depth the customer’s city is at, how many elements charges has, and where the gateway recorded the reason for the decline.

What type is the value: jsonb_typeof

In raw_inbox, the body column has the type text. PostgreSQL sees nothing but characters there, so there’s nothing yet for the JSON functions to work on. You can turn text into a document with a cast: body::jsonb, or the long form CAST(body AS jsonb). At that moment PostgreSQL parses the string according to JSON rules and builds a tree of values.

After that, you can ask the database what came out. The function jsonb_typeof returns one of six words: object, array, string, number, boolean, or null.

But the raw stream has a problem: nobody checked what was written into it, so some of the rows aren’t JSON at all. If you cast the whole column to jsonb at once, the query stops at the first bad row:

SELECT msg_id, jsonb_typeof(body::jsonb) AS kind
FROM raw_inbox;

ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.

The first culprit is message 7, which is empty. Of the twelve messages, seven hold valid JSON: messages 1 through 6, and message 10. In chapter 5, we’ll build a check that finds such rows without crashing the query. For now, we’ll list the numbers of the seven known messages explicitly.

Prediction first. Messages 1 and 2 start with a curly brace, so they’re objects. Message 3 starts with a square bracket: an array. That leaves 4, 5, and 6. Look closely at the fourth: its whole text is wrapped in double quotes.

[], 42, "hello", and null are JSON too

People who have only ever seen JSON in API responses often get this wrong. A service usually returns an object, sometimes an array, and a mistaken habit sets in: you start expecting curly or square brackets every time.

Under the current JSON standard, RFC 8259, any value can sit at the top level: a number, a string, true, false, null. The old RFC 4627 specification from 2006, on the other hand, really did require an object or an array. That’s where the rule comes from, and it still surfaces in code and conversations. PostgreSQL follows the modern version and accepts scalars.

Message 4 is especially telling. The sender turned an object into text and then packed that text into a JSON string once more. This has a name: double serialization. I’ve run into it in webhooks more than once. You can plainly see a document inside the string, but PostgreSQL, for now, treats the whole thing as a single string with no keys to reach into.

Message 6 is a different trap. The cell holds four characters: n, u, l, l. After ::jsonb, they become the JSON value null. SQL NULL works differently: the body column is declared NOT NULL, and this particular cell is definitely filled in. We’ll come back to the difference between the two kinds of “nothing” in the third lesson of the chapter.

Now let’s check this on short literals. Below are six strings: we cast each one to jsonb, then ask for its type. Before you run it, mark which strings you think will pass. And decide the fate of 'null' separately: will it be an error, a SQL NULL, or a value of a third kind?

text, json, jsonb: three ways to store a document

PostgreSQL can store the very same JSON in three ways. With a short document, the difference is barely noticeable. But once you start reading fields, comparing documents, or parsing a raw stream, the type starts to affect the result.

text simply stores characters and doesn’t check whether they form valid JSON. That’s why raw_inbox.body can happily contain an empty string, a complaint about a courier, and JSON with a trailing OK side by side. If you later want to treat that content as a document, you’ll have to cast it explicitly.

json checks the syntax when the value is written, then preserves the original text character for character: whitespace, key order, and even repeated keys stay in place. When you read a field, PostgreSQL parses the stored text again.

jsonb parses the document when it is written and stores the resulting tree in binary form. Fields are faster to read from it, documents can be compared, and indexes can be built for searching them — we’ll get to indexes in chapter 6. But once it has been stored this way, you can no longer reconstruct the original string. Extra whitespace disappears, the database prints keys in its own order, and when keys repeat, only the last value is kept.

The PostgreSQL documentation recommends jsonb for most application use cases. json is needed less often, for example when you need to preserve the original key order. In our dataset, all four columns that store parsed documents are jsonb.

Let’s take a string deliberately constructed to make the differences visible. After 18, there are three spaces in a row, the qty key appears twice, and product_id comes first. The spaces are added with repeat(' ', 3): the function repeats a space three times, and || joins the parts of the string. That makes the number of spaces visible right in the query. Before you run it, try to predict what text will end up in each of the three columns after the cast.

The as_json and as_jsonb expressions end with ::text. This turns all three results into plain text, so you can compare them by eye, character by character, exactly as PostgreSQL returns them.

A repeated key in a real message

The previous literal was made up for the experiment, but repeated keys turn up in real data too. A partner sent message 10 at 23:44. First it records status with the value "queued", then a data object holding an event, and at the end status again, now with the value "sent". Judging by the record, the partner’s system appended the new status to the end of the document and didn’t delete the old one.

The JSON standard doesn’t explicitly forbid this, but it warns that programs may handle a repeated key in different ways. PostgreSQL behaves predictably for each of its two types: json keeps both values, and jsonb keeps the last one.

Lock in your prediction before you run it. Which status will remain in as_jsonb? How many times will status show up in as_json? And watch the order: which key will PostgreSQL print first in as_jsonb?

Key order and whitespace

We’ve already caught jsonb rearranging keys twice. In the demo literal, qty ended up before product_id. In the order from the scene, schema_version was printed at the very bottom, even though it came first in the original event. The reason is the internal representation of jsonb: PostgreSQL stores an object in its own order and prints it that way too. Here, shorter keys in bytes come first, and keys of the same length are ordered byte by byte. The documentation doesn’t guarantee this order, so you can’t build logic around it.

The original whitespace isn’t preserved either. When PostgreSQL prints jsonb, it inserts exactly one space after each colon and comma, no matter how many spaces were in the input string.

Below are three messages from the raw stream: the original text on the left, the result after jsonb on the right. First, try to predict what will change. In message 1, the keys are written as event, user_id, product_id. In message 2, event comes first, followed by user. Message 3 is different: it’s an array of two objects. Think separately about whether PostgreSQL is allowed to rearrange the elements of the array itself.

Comparing documents by content

Now it’s clear why comparing JSON as plain text is risky. Two documents can contain the same keys and values yet differ in whitespace or key order. A string comparison with = sees different characters. Comparing two jsonb values checks the content and, in a case like this, returns true.

Equality has two limits that are easy to forget. In an array, element order is part of the content, so [6, 7] and [7, 6] aren’t equal. The type of a value counts too: the number 1290 differs from the string "1290", even though the two look the same to the eye.

json has no equality operator at all. The query body::json = body::json ends with the error operator does not exist: json = json, and trying to use DISTINCT or GROUP BY on json gives could not identify an equality operator for type json. For documents you need to compare and group, jsonb is noticeably more convenient.

Below are three pairs, and we’ll compare each one twice: as text and as jsonb. You can solve the first pair right away. The keys and values match, only the order and whitespace differ, so same_text will be false and same_jsonb true. For the second and third pairs, write down your answer first, then press “Run.”

The final task of the lesson. An integrator writes to Kotomarket support: “We sent you {"product_id":12,"event":"cart","user":{"id":23}} by webhook. Did it arrive?” The analyst on duty compared the text they sent with every valid message and answered: no.

The query below runs without errors, but all seven rows return false. Before you change anything, look through the raw stream and find the message whose content resembles the integrator’s document.

Common mistakes

Assuming JSON is only an object

Row: message 5, the text 42.

Expectation: there are no braces or keys, so it doesn’t seem to be JSON.

A quick check breaks that assumption: jsonb_typeof(body::jsonb) returns number. JSON allows more than just objects at the top level. In our raw stream, four valid messages came in other forms: 3 is an array, 4 is a string, 5 is a number, and 6 is null.

What to do: before parsing a column, count the top-level types. If you see anything besides object, one query applied to every row may produce the wrong result.

Mixing up JSON null and SQL NULL

Row: message 6, the text null.

Expectation: the condition body IS NULL will find it.

Run the check — the result is false. The cell contains four letters that become the JSON value null after the cast. SQL NULL means something different: there is no value in the cell itself.

What to do: for JSON null, jsonb_typeof returns the string null, while for a real SQL NULL, the result is NULL itself. We’ll look at this pair more closely in the third lesson.

Casting the whole raw column to jsonb

Row: message 7, empty text.

Expectation: body::jsonb will parse the good rows and return NULL for the garbage.

In practice, the whole query fails with the error invalid input syntax for type json. A cast can’t silently skip a bad row: the first error stops execution.

What to do: for now, cast only the rows you already know are valid. In chapter 5, we’ll build a safe check for the whole column.

Expecting jsonb to preserve the original form

Row: message 10, the status key appears twice.

Expectation: after ::jsonb, the value will remain exactly as the partner sent it.

But "queued" disappears, and the keys are printed in a different order. jsonb stores the parsed content and no longer remembers the original string.

What to do: if you need the original representation for an audit, a webhook signature, or investigating someone else’s error, store it separately as text or json.

Comparing documents as strings

Row: message 2 and the integrator’s document.

Expectation: identical content should produce identical strings.

Here the key order and whitespace differ, so a plain = returns false.

What to do: compare jsonb with jsonb. [6, 7] and [7, 6] will still be different, as will the number 1290 and the string "1290".

Taking it to work: meeting a JSON column for the first time

When I open an unfamiliar column of documents for the first time, I usually run four checks before building any reports. They take a few minutes and are very good at catching nasty surprises like message 4 or the empty row 7.

First I look at the column’s type. For text, json, and jsonb, the queries that follow will differ: some need a cast, and for raw text you also have to make sure every row survives it. You can see the type in the table’s schema or check it with pg_typeof(column).

Then I open two or three documents with jsonb_pretty. Usually a short one, a long one, and one from the middle of the period. With a single row it’s easy to decide that all the documents are built the same way; two or three often show different branches or an unexpected type.

After that I count the top-level types:

SELECT jsonb_typeof(payload) AS kind, count(*) AS events
FROM app_events
GROUP BY jsonb_typeof(payload)
ORDER BY events DESC;

For app_events, you get one row: all 36 events are objects. That’s a good result, but it’s better to see the number 36 in the output than to assume it from the first two events. The raw stream tells a different story: of the seven valid messages, only three turned out to be objects.

Finally, I decide whether I’ll ever need the original text. If a document comes from someone else’s system and might later play a part in settling a dispute or an incident, I keep the raw version next to the jsonb, for example in an adjacent column.