https://github.com/Florents-Tselai/pgJQ Skip to content Toggle navigation Sign in * Product + Actions Automate any workflow + Packages Host and manage packages + Security Find and fix vulnerabilities + Codespaces Instant dev environments + Copilot Write better code with AI + Code review Manage code changes + Issues Plan and track work + Discussions Collaborate outside of code Explore + All features + Documentation + GitHub Skills + Blog * Solutions For + Enterprise + Teams + Startups + Education By Solution + CI/CD & Automation + DevOps + DevSecOps Resources + Learning Pathways + White papers, Ebooks, Webinars + Customer Stories + Partners * Open Source + GitHub Sponsors Fund open source developers + The ReadME Project GitHub community articles Repositories + Topics + Trending + Collections * Pricing Search or jump to... Search code, repositories, users, issues, pull requests... Search [ ] Clear Search syntax tips Provide feedback We read every piece of feedback, and take your input very seriously. [ ] [ ] Include my email address so I can be contacted Cancel Submit feedback Saved searches Use saved searches to filter your results more quickly Name [ ] Query [ ] To see all available qualifiers, see our documentation. Cancel Create saved search Sign in Sign up You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session. Dismiss alert {{ message }} Florents-Tselai / pgJQ Public * Notifications * Fork 3 * Star 121 * jq extension for Postgres tselai.com/pgjq-dsl-database.html License MIT license 121 stars 3 forks Branches Tags Activity Star Notifications * Code * Issues 2 * Pull requests 0 * Actions * Projects 0 * Security * Insights Additional navigation options * Code * Issues * Pull requests * Actions * Projects * Security * Insights Florents-Tselai/pgJQ This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. main BranchesTags Go to file Code Folders and files Name Name Last commit message Last commit date Latest commit History 11 Commits sql sql test test .gitignore .gitignore LICENSE LICENSE Makefile Makefile README.md README.md pgjq-demo.gif pgjq-demo.gif pgjq.c pgjq.c pgjq.control pgjq.control View all files Repository files navigation * README * MIT license pgJQ: Use jq in Postgres [6874747073] GitHub Repo stars The pgJQ extension embeds the standard jq compiler and brings the much loved jq lang to Postgres. It adds a jqprog data type to express jq programs and a jq(jsonb, jqprog) function to execute them on jsonb objects. It works seamlessly with standard jsonb functions, operators, and jsonpath. SELECT jq('[{"bar": "baz", "balance": 7.77, "active":false}]'::jsonb, '.[0].bar'); jq ------- "baz" (1 row) til Usage Filters You can run basic filters: SELECT jq('[{"bar": "baz", "balance": 7.77, "active":false}]'::jsonb, '.[0].bar'); jq ------- "baz" (1 row) jsonb @@ jqprog If you're a syntactic sugar addict, you can use the @@ operator to achieve the same. It's better be explicit with the ::jqprog when using operators. SELECT '[{"bar": "baz", "balance": 7.77, "active":false}]' @@ '.[0].bar'::jqprog; jq ------- "baz" (1 row) Complex Programs You can run more complex jq programs too: SELECT jq('[true,false,[5,true,[true,[false]],false]]', '(..|select(type=="boolean")) |= if . then 1 else 0 end'); jq ----------------------------- [1, 0, [5, 1, [1, [0]], 0]] (1 row) SELECT jq('[1,5,3,0,7]' , '(.[] | select(. >= 2)) |= empty'); jq -------- [1, 0] (1 row) Passing Arguments to jqprog If you want to pass dynamic arguments to jqprog, you can pass them as a jsonb object and refer to them as $var. select jq('{ "runner": 1, "message": "jobStatus", "jobs": [ { "id": 9, "is_successfull": true }, { "id": 100, "is_successfull": false, "metdata": { "environ": "prod" } } ] }'::jsonb, '.jobs[] | select(.is_successfull == $is_success and .id == 100) | .', '{"is_success": false, "id": 100}'); jq ---------------------------------------------------------------------- {"id": 100, "metdata": {"environ": "prod"}, "is_successfull": false} (1 row) jq and jsonpath You can even chain jq and jsonpath together! Note here that the later part - '{trans}' @> '{"cust": "baz"}' is jsonpath, not jq code. SELECT jq('[ { "cust": "baz", "trans": { "balance": 100, "date": "2023-08-01" }, "active": true, "geo": { "branch": "paloukia" } } ]', '(.[] | select(.active == true))') - '{trans}' @> '{"cust": "baz"}'; ?column? ---------- t (1 row) If you opt for using operators here, you should help the parser by adding parentheses and explicit casts. SELECT ('[ { "cust": "baz", "trans": { "balance": 100, "date": "2023-08-01" }, "active": true, "geo": { "branch": "paloukia" } } ]' @@ '(.[] | select(.active == true))'::jqprog) - '{trans}' @> '{"cust": "baz"}'; It is strongly recommended to be explicit with type casts and ordering when using overloaded operators, especially when you're working a lot with text. Otherwise, you'll find yourself in an obfuscated labyrinth of jqprogs, jsonbs, jsonpaths and possibly tsvectors , impossible to escape from. Working with Files If you have superuser privileges in Postgres you can use the pg_read_file to run your queries on JSON files. SELECT jq(pg_read_file('/path/to/f.json', '.[]')) You can see more examples in the test cases or try reproducing the jq manual . Installation git clone https://github.com/Florents-Tselai/pgJQ.git cd pgJQ make install # set PG_CONFIG=/path/to/bin/pg_config if necessary. make installcheck In a Postgres session run CREATE EXTENSION pgjq How it Works pgJQ does not re-implement the jq lang in Postgres. It instead embeds the standard jq compiler and uses it to parse jq programs supplied in SQL queries. These programs are fed with jsonb objects as input. Issues jq has evolved from just a cli tool to a full-fledged DSL, but it still remains a 20-80 tool. pgJQ has been TDDed against those 20% of the cases. If you come across regressions between vanilla jq and pgJQ, especially around piped filters or complex functions, please do add an issue, along with a test case! Keeping in mind, though, that there's probably not much point reproducing the whole DSL in an RDBMS context. Some known issues are: * Only string, bool and numeric arguments can be passed to jqprog. * Currently, jq programs including pipes, like .[] | .name are buggy and unpredictable. * Modules are not supported, but they could be theoretically supported, given that Postgres is fairly open to dynamic loading. About jq extension for Postgres tselai.com/pgjq-dsl-database.html Topics postgres json dsl jq Resources Readme License MIT license Activity Stars 121 stars Watchers 4 watching Forks 3 forks Report repository Releases No releases published Packages 0 No packages published Contributors 2 * @Florents-Tselai Florents-Tselai Florents Tselai * @timwmillard timwmillard Tim Millard Languages * C 96.1% * Makefile 3.9% Footer (c) 2024 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact * Manage cookies * Do not share my personal information You can't perform that action at this time.