base on jq extension for Postgres # pgJQ: Use `jq` in Postgres <a href="https://hub.docker.com/repository/docker/florents/pgjq"><img alt="Docker Pulls" src="https://img.shields.io/docker/pulls/florents/pgjq"></a> <img alt="GitHub Repo stars" src="https://img.shields.io/github/stars/Florents-Tselai/pgJQ"> <a href="https://www.linkedin.com/in/florentstselai/"><img src="https://img.shields.io/badge/LinkedIn-0077B5?logo=linkedin&logoColor=white"></a> <a href="https://github.com/sponsors/Florents-Tselai/"><img src="https://img.shields.io/static/v1?label=Sponsor&message=%E2%9D%A4&logo=GitHub&link=https://github.com/sponsors/Florents-Tselai/"></a> <a href="https://opensource.org/licenses/MIT license"><img src="https://img.shields.io/badge/MIT license-blue.svg"></a> *Note*: If you like this idea check out: [liteJQ: jq extension for SQLite](https://github.com/Florents-Tselai/liteJQ) The **pgJQ** extension embeds the standard jq compiler and brings the much loved [jq](https://github.com/jqlang/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`. ```sql SELECT jq('[{"bar": "baz", "balance": 7.77, "active":false}]'::jsonb, '.[0].bar'); ``` ``` jq ------- "baz" (1 row) ```  ## Usage ### Filters You can run basic filters: ```sql 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. ```sql 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: ```sql 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) ``` ```sql 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`. ```sql 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. ```sql 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. ```sql 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 `jqprog`s, `jsonb`s, `jsonpath`s and possibly `tsvector`s , 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. ```sql SELECT jq(pg_read_file('/path/to/f.json', '.[]')) ``` You can see more examples in the [test cases](test/sql/basic.sql) or try reproducing the [`jq` manual](https://jqlang.github.io/jq/manual/) . ## Installation ```sh 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 ```sql 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. ", Assign "at most 3 tags" to the expected json: {"id":"8811","tags":[]} "only from the tags list I provide: [{"id":77,"name":"3d"},{"id":89,"name":"agent"},{"id":17,"name":"ai"},{"id":54,"name":"algorithm"},{"id":24,"name":"api"},{"id":44,"name":"authentication"},{"id":3,"name":"aws"},{"id":27,"name":"backend"},{"id":60,"name":"benchmark"},{"id":72,"name":"best-practices"},{"id":39,"name":"bitcoin"},{"id":37,"name":"blockchain"},{"id":1,"name":"blog"},{"id":45,"name":"bundler"},{"id":58,"name":"cache"},{"id":21,"name":"chat"},{"id":49,"name":"cicd"},{"id":4,"name":"cli"},{"id":64,"name":"cloud-native"},{"id":48,"name":"cms"},{"id":61,"name":"compiler"},{"id":68,"name":"containerization"},{"id":92,"name":"crm"},{"id":34,"name":"data"},{"id":47,"name":"database"},{"id":8,"name":"declarative-gui "},{"id":9,"name":"deploy-tool"},{"id":53,"name":"desktop-app"},{"id":6,"name":"dev-exp-lib"},{"id":59,"name":"dev-tool"},{"id":13,"name":"ecommerce"},{"id":26,"name":"editor"},{"id":66,"name":"emulator"},{"id":62,"name":"filesystem"},{"id":80,"name":"finance"},{"id":15,"name":"firmware"},{"id":73,"name":"for-fun"},{"id":2,"name":"framework"},{"id":11,"name":"frontend"},{"id":22,"name":"game"},{"id":81,"name":"game-engine "},{"id":23,"name":"graphql"},{"id":84,"name":"gui"},{"id":91,"name":"http"},{"id":5,"name":"http-client"},{"id":51,"name":"iac"},{"id":30,"name":"ide"},{"id":78,"name":"iot"},{"id":40,"name":"json"},{"id":83,"name":"julian"},{"id":38,"name":"k8s"},{"id":31,"name":"language"},{"id":10,"name":"learning-resource"},{"id":33,"name":"lib"},{"id":41,"name":"linter"},{"id":28,"name":"lms"},{"id":16,"name":"logging"},{"id":76,"name":"low-code"},{"id":90,"name":"message-queue"},{"id":42,"name":"mobile-app"},{"id":18,"name":"monitoring"},{"id":36,"name":"networking"},{"id":7,"name":"node-version"},{"id":55,"name":"nosql"},{"id":57,"name":"observability"},{"id":46,"name":"orm"},{"id":52,"name":"os"},{"id":14,"name":"parser"},{"id":74,"name":"react"},{"id":82,"name":"real-time"},{"id":56,"name":"robot"},{"id":65,"name":"runtime"},{"id":32,"name":"sdk"},{"id":71,"name":"search"},{"id":63,"name":"secrets"},{"id":25,"name":"security"},{"id":85,"name":"server"},{"id":86,"name":"serverless"},{"id":70,"name":"storage"},{"id":75,"name":"system-design"},{"id":79,"name":"terminal"},{"id":29,"name":"testing"},{"id":12,"name":"ui"},{"id":50,"name":"ux"},{"id":88,"name":"video"},{"id":20,"name":"web-app"},{"id":35,"name":"web-server"},{"id":43,"name":"webassembly"},{"id":69,"name":"workflow"},{"id":87,"name":"yaml"}]" returns me the "expected json"