Json Data

By the end of this lesson you'll be able to store flexible, differently-shaped data inside a single SQL column, then reach into nested fields, filter rows by what's inside the JSON, modify documents in place, and index them so those queries stay fast — in both PostgreSQL and MySQL.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every query in this lesson runs against this tiny events table. Each row's payload is a jsonb document — and notice the rows are not all the same shape. That is exactly what JSON columns are for.

Sometimes your data doesn't fit a neat grid. An event log might record purchases (with items and a total), signups (with a traffic source), and errors (with a stack trace) — each a different shape. Rather than inventing dozens of mostly-empty columns, you can store the whole thing as a JSON document in one column.

JSON ("JavaScript Object Notation") is the familiar {' "key": value '} text format. SQL treats the text between the quotes as a value to validate and store; from then on you query into it with special operators.

Normal columns are a filing cabinet with a labelled drawer for every field — rigid, but instantly findable. A jsonb column is a labelled box you can drop any shaped folder into. Flexible, but you pay a little to find things inside it (that's what indexes fix).

PostgreSQL gives you two JSON types and the difference matters. json stores the document as raw text — it keeps your exact spacing and key order, but must be re-parsed on every read and cannot be indexed for searching. jsonb stores a binary , decomposed form: a touch slower to write, far faster to query, it strips duplicate keys, and it supports GIN indexing.

These four operators are the heart of working with JSON, and the single most important thing to learn is which ones return JSON and which return text :

A field key uses 'quotes' ; an array element uses its number, so payload -> 'items' ->> 0 is "the first item, as text".

Fill in the blanks to pull the top-level type field out of every event as text . The expected result is in the comments so you can check yourself.

You can use the same operators inside a WHERE clause to keep only the rows whose JSON matches. The catch: ->> always hands you text , so compare against a quoted string — or cast to a number/date when the type matters. Comparing '999.99' > '100' as text gives the wrong answer because text sorts character by character.

One concept this time — keep only the events placed by a gold-tier customer. The tier lives at customer.tier .

Spelling out a path for every condition gets tedious. The containment operator @> asks "does the left document contain this smaller JSON?" — it matches nested keys and array elements in one shot, and (unlike a chain of ->> ) it can use a GIN index.

To change a document, jsonb_set(target, path, new_value) returns a modified copy . The path is a text array ( {' customer,tier '} means customer → tier ), and new_value must itself be JSON — so a string needs its quotes: '"platinum"' . To bulk-merge fields use || ; to drop a key use the - operator.

Without an index, every @> or ? query reads the whole table. A GIN index (Generalized INverted index) catalogues the keys and values inside your documents so those lookups become fast.

A plain USING gin (payload) index powers the containment and key-existence operators. If you only ever use @> , the smaller jsonb_path_ops variant is faster. And when you constantly filter one extracted field by equality, a B-tree index on (payload ->> 'type') beats both. Use EXPLAIN ANALYZE to confirm the planner actually picks your index.

MySQL has a single JSON type that behaves much like PostgreSQL's jsonb — binary and validated. The operators differ: you extract with JSON_EXTRACT(doc, '$.path') or its shorthand -> (both return JSON), and ->> is the text version (shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) ). Paths use the $.key / $.arr[0] syntax.

The standout feature is JSON_TABLE , which explodes a JSON array into ordinary rows you can join and aggregate — turning a nested array into a relational result set.

JSON columns are powerful, but they are not a replacement for good schema design. If you filter, sort, join, or group by a value all the time — or it needs a constraint or a real date/money type — promote it to a proper column. Keep JSON for the genuinely variable, rarely-queried parts. The best real-world tables are hybrid : hot fields as columns, the long tail as jsonb .

Use ->> whenever you want a usable value — to display it, compare it, or cast it. Use -> only to dig deeper, because it keeps the result as JSON so you can chain another arrow onto it.

Extracted values come out as text, and text sorts character by character (so '9' looks bigger than '30' ). Cast first: (payload ->> 'total')::numeric .

jsonb almost always. It's indexable and far faster to query. Only pick json if you must return the document with its original whitespace and key order intact.

No. Anything you filter, join, or sort on a lot — or that needs a constraint or a real type — should be a normal column. Use JSON for the variable, rarely-queried extras and you get the best of both worlds.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary