How to Parse JSON in PostgreSQL

Why store JSON in PostgreSQL?

PostgreSQL’s native json and jsonb types let you keep semi-structured data without losing SQL power. You can index, query, and join JSON alongside relational columns, ideal for evolving ecommerce schemas.

Which data type should I choose—json or jsonb?

json preserves raw text; jsonb stores a binary representation that supports indexing and faster lookups. For most workloads choose jsonb.

How do I extract a single field?

Use ->> for text, -> for JSON. Example: SELECT details ->> 'status' FROM Orders;

Can I drill into nested objects?

Yes. #> and #>> accept path arrays. Example: details #>> '{shipping,address,city}' returns the city as text.

How do I unnest JSON arrays?

jsonb_array_elements() or json_array_elements() turns each array element into a row—perfect for order line items.

What about casting values?

Cast text to the target type: (details ->> 'total_amount')::numeric. Always cast before comparing numerics or dates.

How do I filter rows by JSON content?

Combine operators with WHERE. Example: WHERE details ->> 'status' = 'shipped'.

Best practices for JSON columns

1) Prefer jsonb. 2) Create GIN indexes with jsonb_path_ops when reading often. 3) Keep top-level keys stable to simplify queries. 4) Cast early in complex expressions.

How can I update a JSON key?

Use jsonb_set(): UPDATE Orders SET details = jsonb_set(details, '{status}', '"delivered"', false) WHERE id = 42;

Common mistakes and fixes

Mistake 1: querying json with GIN indexes—only jsonb is supported.
Fix: migrate column to jsonb.

Mistake 2: forgetting to cast text values before numeric comparison.
Fix: use ::numeric, e.g., (details ->> 'total_amount')::numeric > 100.

Need a quick recap?

Use ->/->> for single keys, #>/#>> for paths, jsonb_array_elements() for arrays, and always cast when comparing.

FAQ

Does jsonb_set() create missing keys?

Yes, when its fourth argument is true; otherwise it throws an error.

Can I index a specific JSON path?

Yes, create a functional index like CREATE INDEX idx_status ON Orders ((details ->> 'status'));

Is JSON parsing slower than relational columns?

JSON adds overhead, but jsonb with proper indexes delivers millisecond reads for most workloads.