---
title: "SQL or NoSQL? Why not use both (with PostgreSQL)?"
description: "The post compares SQL and NoSQL storage for an application that records daily food, water, exercise, weight, and notes, focusing on the tension between easy document writes and later analysis. It proposes using PostgreSQL as a hybrid: keep core fields such as date, user_id, weight, and notes in relational columns, while storing food_log, water_log, and exercise_log as JSONB fields. For analysis, PostgreSQL's jsonb_array_elements expands arrays into rows; ->> extracts text, -> returns JSON, and numeric values such as calories must be cast to INTEGER before aggregation, with a common table expression used to work around the restriction on set-returning functions inside aggregate calls. The examples show daily calorie totals and a search for garlic bread returning 200 calories, supporting the conclusion that JSONB can simplify storage while SQL preserves querying and analysis capabilities."
---

# SQL or NoSQL? Why not use both (with PostgreSQL)?

[Supabase](https://yomu.fyi/company/supabase) · burggraf · Nov 24, 2022

**Type:** Explainer

## Summary

The post compares SQL and NoSQL storage for an application that records daily food, water, exercise, weight, and notes, focusing on the tension between easy document writes and later analysis. It proposes using PostgreSQL as a hybrid: keep core fields such as date, user\_id, weight, and notes in relational columns, while storing food\_log, water\_log, and exercise\_log as JSONB fields. For analysis, PostgreSQL's jsonb\_array\_elements expands arrays into rows; ->> extracts text, -> returns JSON, and numeric values such as calories must be cast to INTEGER before aggregation, with a common table expression used to work around the restriction on set-returning functions inside aggregate calls. The examples show daily calorie totals and a search for garlic bread returning 200 calories, supporting the conclusion that JSONB can simplify storage while SQL preserves querying and analysis capabilities.

## Context

The post addresses the choice between SQL and NoSQL for complex, hierarchical application data. NoSQL can make it easier to save and retrieve a day's JSON document, while SQL makes later business queries and aggregations easier. The example application has variable numbers of food, water, and exercise records per day, plus zero or one weight-and-notes record.

## Approach / What changed

The proposed design uses one PostgreSQL calendar table per user's day, with relational columns for date, user\_id, weight, and notes, and JSONB columns for food\_log, water\_log, and exercise\_log. PostgreSQL JSON functions flatten nested arrays for querying. The examples use jsonb\_array\_elements, the -> and ->> operators, INTEGER casts for calories, and a common table expression before aggregation.

## Takeaways

- PostgreSQL JSONB fields can store complex food, water, and exercise structures directly while relational columns retain fields such as date, user\_id, weight, and notes.
- jsonb\_array\_elements expands a JSONB array into rows; ->> returns text, while -> returns a JSON value that may need casting before numeric operations.
- A common table expression avoids PostgreSQL's error when a set-returning JSON function is placed directly inside an aggregate function, enabling daily calorie totals such as 1,838.

**Tags:** [Data Pipelines](https://yomu.fyi/topic/data-pipelines), [Performance](https://yomu.fyi/topic/performance), [Postgres](https://yomu.fyi/topic/postgres)

- Source: [Supabase](https://supabase.com/blog/sql-or-nosql-both-with-postgresql)
- Source URL: https://supabase.com/blog/sql-or-nosql-both-with-postgresql
- Ingested by Yomu: 2026-08-29T01:14:57.592Z

[Read original post](https://supabase.com/blog/sql-or-nosql-both-with-postgresql)