编程与软件工程sqlite.org
SQLite 的 JSON 功能
JSON with Sqlite
2017 — 2019，它一次次回到首页
黑点是上首页（≥ 50 分）的那次投稿，空心点是没冲上去的投稿，点越大分数越高。
导读
这是 SQLite 官方 JSON1 扩展的文档，介绍如何在 SQL 查询中直接创建、解析和操作 JSON 数据（如 json()、json_each()、json_extract() 等函数）。它让开发者可以在 SQLite 中以类 NoSQL 的方式存储和查询半结构化数据，而无需在应用层解析 JSON。
为什么经久不衰：SQLite 内置 JSON 支持让轻量级数据库也能处理半结构化数据，配合表达式索引性能出色，对微服务和数据分析场景至今仍非常实用。
HN 评论精选
JSON1 + 表达式索引让 SQLite 能以 NoSQL 风格高效处理数据，很多场景无需上重型数据库
我已在生产项目中使用 JSON1 扩展一段时间，非常出色。我通常把 API 返回的 JSON 直接存到一张 raw_data 表里（一列 updated_at，一列 json_data）。你不必急着规范化 schema——通过在表达式上精心建索引，完全可以获得类 NoSQL 反范式 schema 的性能。数据在 SQL 里就地查询几乎总是比取到磁盘外再用 Python 处理快，IO 成本太高（所以别把所有数据塞进巨大的 DataFrame，去学 SQL 吧）。对于读多写少的数仓类应用（如内部报表），没理由去搭 Postgres 或 MongoDB。
fforflo查看英文原文
I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wise, by carefully defining index on expressions. You can somehow normalize it with views (SQLite doesn't support materialized views). And of course it's almost always faster to query data where it exists (via SQL) instead of fetching it from disk and querying it say Python. You pay too much IO cost. (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) The json dump is not stored in binary, but in text format, but honestly I haven't seen this to be a problem, plus you can easily run queries at the CLI and pipe the output to jq, sed etc. If your application is data warehouse-like and read-heavy (for example an internal reporting dashboard) I can't see any reason why you should pay the cost of setting up a Postgres or MongoDB instance (although I do love both.) It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure).
澄清 SQLite 的 JSON 支持与 Postgres 的差异：它是查询语言扩展，不能直接按 JSON 结构建索引
需要说明的是，这个功能不等同于 Postgres 的 JSON 支持——Postgres 可以基于 JSON 结构建索引，而这是对查询语言的扩展，在查询中支持编解码，例如 SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%'。考虑到他们完全自己实现了这一切，相当不错。如果没看过 SQLite 源码，去看看他们的测试。
jzelinskie查看英文原文
It should be made clear that this functionality is not equivalent to the Postgres JSON support where you can create indexes based on the structure of the JSON. This is an extension to the query language that adds support for encoding/decoding within the context of a query: SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%'; It's pretty neat considering they rolled it all on their own: https://www.sqlite.org/src/artifact/552a7d730863419e PS: If you haven't looked at the SQLite source before, check out their tests.
SQLite 无网络层，避免了分布式数据库常见的连接故障，适合极简架构
SQLite 是微服务和其他极简架构的绝佳数据库。你肯定不会从 SQLite 那里得到 'TCP socket failed/timeout/reset' 这类错误。
nbevans查看英文原文
SQLite is a great database for microservices and other minimalist architectures. You'll never get a "TCP socket failed/timeout/reset" from SQLite - that's for sure.
提供检查和启用 JSON1 扩展的实用方法
要检查你是否已安装该扩展，运行 pragma compile_options; 并查找 ENABLE_JSON1。我的 Mac 上终端的 sqlite3 和 Python 里都有。最新版 sqlite3 已内置；如果没有，可以按教程自行编译。
punnerud查看英文原文
To check if you already have it installed run "pragma compile_options;" and look for ENABLE_JSON1. On my Mac I had it installed both on sqlite3 in terminal/bash and Python :-) import sqlite3 con = sqlite3.connect(':memory:') con.enable_load_extension(True) a = con.execute("pragma compile_options;") for i in a: print(i); #check for ENABLE_JSON1 The newest releases of sqlite3 have it included. If not you can build it this way: https://burrows.svbtle.com/build-sqlite-json1-extension-as-shared-library-on-os-x Note that you will only get this error on load if you have it: 'Error: error during initialization:'
历次讨论
同类还值得读
开源应用架构
The Architecture of Open Source Applications
《开源软件架构》（AOSA）是一本在线免费书，邀请知名开源项目的作者亲自剖析其内部设计，涵盖 SQLAlchemy、LLVM、Audacity、Apache、Bash 等系统。
编程与软件工程上首页 9 次 · 2011–2024aosabook.org编辑精选编程语言简史：简短、不完整且多半错误
A Brief, Incomplete, and Mostly Wrong History of Programming Languages
一篇以戏谑、故意不准确的方式回顾编程语言史的博客文章，用夸张轶事和讽刺定义串起 Lisp、C、C++、Java、JavaScript、PHP 等语言。
编程与软件工程上首页 8 次 · 2009–2021james-iry.blogspot.comSQLite 是如何测试的
How SQLite is tested
SQLite 官方文档页，系统介绍 SQLite 的测试体系：多种测试方法（TCL 测试、模糊测试、回归测试、静态分析等）和海量测试用例，展示了这个小型开源项目如何靠极致测试获得…
编程与软件工程上首页 8 次 · 2009–2025sqlite.org编辑精选高级编程语言（2009）
Advanced programming languages (2009)
Matt Might 的经典文章，按用途推荐“高级编程语言”：如果追求就业就选主流语言，如果追求表达力、学术研究或创业，则推荐 Haskell、OCaml、Scala 等函数式语…
编程与软件工程上首页 8 次 · 2009–2022matt.might.net我的 Forth 与栈机器经历
Yosefk - My history with Forth & stack machines
作者以亲身经历回顾了使用 Forth 语言和栈式机器的历程：既赞叹其简洁优雅、代码量小、可自举的优点，也坦率指出栈操作在实际编程中的痛苦，以及 Forth 社区文化中的问题。
编程与软件工程上首页 8 次 · 2010–2024yosefk.com编辑精选