Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

start docs#5

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Open
niquola wants to merge4 commits intopostgrespro:master
base:master
Choose a base branch
Loading
fromniquola:master
Open
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
1 change: 1 addition & 0 deletions.gitignore
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -6,3 +6,4 @@ jsquery_gram.h
regression.diffs
regression.out
results
*sw?
8 changes: 8 additions & 0 deletions.travis.yml
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
before_script:
- sudo su $USER -c ".travis/install"

script: sudo su $USER -c ".travis/test"

addons:
postgresql: "9.3"

47 changes: 47 additions & 0 deletions.travis/install
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
#!/bin/bash

export ROOT=`pwd`
export BUILD_DIR=/home/travis/build/pg
export SOURCE_DIR=$BUILD_DIR/src

export PGDATA=$BUILD_DIR/data
export PGPORT=5777
export PGHOST=localhost

export PG_BIN=$BUILD_DIR/bin
export PG_CONFIG=$BUILD_DIR

export PATH=$PATH:$PG_BIN

sudo apt-get -y -qq --purge remove postgresql libpq-dev libpq5 postgresql-client-common postgresql-common
sudo apt-get -qq update
sudo apt-get -qqy install \
git \
build-essential \
gettext \
libreadline6 \
libreadline6-dev \
zlib1g-dev \
flex \
bison \
libxml2-dev \
libxslt-dev

git clone -b REL9_4_STABLE --depth=1 git://git.postgresql.org/git/postgresql.git $SOURCE_DIR

# XML2_CONFIG=`which xml2-config` cd $SOURCE_DIR ./configure --prefix=$BUILD_DIR --with-libxml &&\

cd $SOURCE_DIR && ./configure --prefix=$BUILD_DIR && make && make install


cd $ROOT && make USE_PGXS=1 && make install USE_PGXS=1

mkdir -p $PGDATA

$PG_BIN/initdb -D $PGDATA -E utf8

# echo "host all all 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
# echo "listen_addresses='*'" >> $PGDATA/postgresql.conf
# echo "port=$PGPORT" >> $PGDATA/postgresql.conf

$PG_BIN/pg_ctl -D $PGDATA start
17 changes: 17 additions & 0 deletions.travis/test
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
#!/bin/bash

export BUILD_DIR=/home/travis/build/pg
export SOURCE_DIR=$BUILD_DIR/src

export PGDATA=$BUILD_DIR/data
export PGPORT=5777
export PGHOST=localhost

export PG_BIN=$BUILD_DIR/bin
export PG_CONFIG=$BUILD_DIR

export PATH=$PATH:$PG_BIN

export ROOT=`pwd`

make installcheck USE_PGXS=1
56 changes: 56 additions & 0 deletionsDockerfile
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
FROM ubuntu:14.04
MAINTAINER Nikolay Ryzhikov <niquola@gmail.com>

RUN apt-get -qq update
RUN apt-get -qqy install git \
build-essential \
gettext \
libreadline6 \
libreadline6-dev \
zlib1g-dev \
flex \
bison \
libxml2-dev \
libxslt-dev

RUN echo "en_US.UTF-8 UTF-8" > /etc/locale.gen && locale-gen

RUN useradd -m -s /bin/bash db && echo "db:db"|chpasswd && adduser db sudo
RUN echo 'db ALL=(ALL) NOPASSWD: ALL' >> /etc/sudoers

USER db
ENV HOME /home/db
ENV PG_BRANCH REL9_4_STABLE
ENV PG_REPO git://git.postgresql.org/git/postgresql.git

RUN git clone -b $PG_BRANCH --depth=1 $PG_REPO $HOME/src
RUN cd $HOME/src && ./configure --prefix=$HOME/bin && make && make install

ENV SOURCE_DIR $HOME/src

ENV PATH $HOME/bin/bin:$PATH
ENV PGDATA $HOME/data
ENV PGPORT 5432
ENV PGHOST localhost
RUN mkdir -p $PGDATA
RUN initdb -D $PGDATA -E utf8

RUN echo "host all all 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
RUN echo "listen_addresses='*'" >> $PGDATA/postgresql.conf
RUN echo "port=$PGPORT" >> $PGDATA/postgresql.conf

RUN pg_ctl -D $HOME/data -w start && psql postgres -c "alter user db with password 'db';"

RUN pg_ctl -D $HOME/data -w start && \
cd $SOURCE_DIR/contrib/pgcrypto && \
make && make install && make installcheck && \
pg_ctl -w stop

RUN pg_ctl -D $HOME/data -w start && \
cd $SOURCE_DIR/contrib && \
git clone https://github.com/akorotkov/jsquery.git && \
cd jsquery && make && make install && make installcheck && \
pg_ctl -w stop

EXPOSE 5432
CMD pg_ctl -D $HOME/data -w start && psql postgres
108 changes: 108 additions & 0 deletionsREADME.md
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,108 @@
# jsquery

[![Build Status](https://travis-ci.org/niquola/jsquery.svg)](https://travis-ci.org/niquola/jsquery)


`Jsquery` is PostgreSQL extension,
which provides advanced query language for jsonb documents.

Features:

* recursive structure of query
* search in array
* rich set of comparison operators
* types support
* schema support (constraints on keys, values)
* indexes support
* hinting support


Jsquery implemented as datatype `jsquery` and operator `@@`.

Examples:

`#` - any element array

```SQL
SELECT '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b.# = 2';
```

`%` - any key

```SQL
SELECT '{"a": {"b": [1,2,3]}}'::jsonb @@ '%.b.# = 2';
```

`*` - anything

```SQL
SELECT '{"a": {"b": [1,2,3]}}'::jsonb @@ '*.# = 2';
```

`$` - current element

```SQL
select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b.# ($ = 2 OR $ < 3)';
```

Use "double quotes" for key !

```SQL
select 'a1."12222" < 111'::jsquery;
```

## Documentation

* [Syntax](doc/intro.md)
* [Indexes](doc/indexes.md)
* [Optimizer](doc/optimiser.md)

## Installation

Requirements:

* PostgreSQL >= 9.4

### Build from sources

```sh
git clone https://github.com/akorotkov/jsquery.git $SOURCE_DIR/contrib
cd $SOURCE_DIR/contrib/jsquery && make && make install && make installcheck

# or not from pg sources

git clone https://github.com/akorotkov/jsquery.git
cd jsquery && make USE_PGXS=1 && make install USE_PGXS=1

```

### Using docker

TODO

## Roadmap

* Implement jsquery as extension of SQL syntax

## Contribution

You can contribute by:

* stars
* [issues](https://github.com/akorotkov/jsquery/issues)
* documentation
* pull requests

## Authors

* Oleg Bartunov
* Teodor Sigaev
* Alexand Korotkov

## Regards

Development is sponsored by [Wargaming.net]

## License

GPL
Empty file addeddoc/indexes.md
View file
Open in desktop
Empty file.
124 changes: 124 additions & 0 deletionsdoc/intro.md
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,124 @@
# Jsquery intro

```jsquery``` implemented as datatype and `@@` operator.

Search query has a form:

```SQL
SELECT * FROM mytable
WHERE jsonb_column @@ 'jsquery_expression';
```


```jsquery_expression``` usually consists
of *path* and *value_expression*.

For example if we are looking for:

```json
{
"user": {
"name": "Diego"
},
"site": {
"url": "diego.com"
}
}
```

the expression is:

```
user.name = 'diego'
```

Sevral expressions could be connected using boolean `AND` & `OR` operators:

```
user.name = 'diego' AND site.url = 'diego.com'
```

Expression could be negated with `NOT` operator:

```
NOT user.name = 'diego'
```

JSON value could be one following types:

* array
* numeric
* object
* string
* boolean

You can check type using `is` operator:

```
user.name is array
user.name is numeric
user.name is object
user.name is string
user.name is boolean
```

For all types you `=` (equality) operator is defined:

```
user.roles = ["admin","root"]
user.age = 3
user.active = true
user.address = {city: "SPb"}
user.name = "diego"
```

For numerics there are expected comparison operators:

```
x > 1 AND x < 10
x >= 1 AND x <= 10
```

To check that scalar value belongs to some list:

```sql
select '{a: 2}'::jsonb @@ 'a IN (1,2,5)';
```

For arrays there are convenient operators:

```sql
-- overlap
select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b && [1,2,5]';

-- contains
select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b @> [1,2]';

-- contained
select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b <@ [1,2,3,4,5]'
```

If you just want to check that some path exists in json document use `=*`:

select '{"a": {"b": [1,2,3]}}'::jsonb @@ 'a.b = *'


Path expression supports wild cards:

`#` - any alement of array
`%` - any key in object
`*` - any path

```sql
select '{a: {b: {c: 1}}}'::jsonb @@ '*.c = 1'
select '{a: {b: {c: 1}}}'::jsonb @@ 'a.%.c = 1'
select '{a: {b: [1,2]}}'::jsonb @@ 'a.b.# = 1'
```

jsquery expression could be expressed recursively using `()`:

```
address(city = "SPB" AND street = "Nevskiy")
```

This means eval `city = "SPB" AND street = "Nevskiy"` expression in context of `address` attribute.
Loading
[8]ページ先頭
©2009-2025 Movatter.jp