KO | EN

GitLite

Minimalist Open Source Repository Search Engine

gitlite — search

#typescript #ai-agents #developer-tools #cli #codex #claude-code #react #mcp #open-source #deepseek-harness #dsh-plugin #ai

postsql

★ 183 Open GitHub ↗

Functions for transforming PostgreSQL and PL/v8 into a totally awesome JSON document store

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

PostSQL
====================================

Functions for transforming PostgreSQL and PL/v8 into a totally awesome JSON document store

Requirements
------------------------------------

* PostgreSQL 9.2+
 * PL/V8 http://code.google.com/p/plv8js/wiki/PLV8

PG 9.2 gives you a JSON datatype, which will validate JSON automatically.

Any version of PG that supports PL/V8 can be used but the functions will need to be adapted to use TEXT instead of JSON types. Validation of JSON will also obviously need to be handled manually.

Background
------------------------------------

Initial work inspired by a demo by Andrew Dunstan
http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html

And this by @leinweber captures the grand vision 
http://ssql-pgaustin.herokuapp.com/#1

Details
-------------------------------------

In order to be used in WHERE and ORDER BY correctly JSON field, the various JSON Types need to be mapped into corresponding PG types. Using the correctly typed accessor allows PG's normal operators to JUST WORK. 
  
    Number     => INT or DOUBLE PRECISION
    String     => TEXT
    Date       => TIMESTAMP
    Boolean    => BOOLEAN
    Array      => ARRAY of appropriate PG Type 
    Object     => ?
    null       => NULL
 
Functions expect a column of pg JSON type.

All functions will accept a JSON field as a string in dot notation, allowing access to fields of arbtirary depth eg "person.name".

### Scalar Functions

#### json_string(column, field) returns TEXT

SELECT id, json_string(data,'person.name') FROM things WHERE json_string(data,'person.name') = 'Zaphod';
    
    SELECT id, json_string(data,'name') FROM things WHERE json_string(data,'name') LIKE 'G%';
 
#### json_int(column, field) returns INT

JS values that are NaN are returned as NULL

SELECT id, json_int(data,'person.id') FROM things WHERE json_int(data,'person.id') = 10;
    
    SELECT id, json_int(data,'count') FROM things WHERE json_int(data,'count') <= 99;
 
#### json_float(column, field) returns DOUBLE PRECISION
  
JS values that are NaN are returned as NULL

SELECT id, json_int(data,'person.id') FROM things WHERE json_int(data,'person.id') = 10.01;
    
    SELECT id, json_int(data,'count') FROM things WHERE json_int(data,'count') <= 99.9999;

#### json_bool(column, field) returns BOOLEAN

Literal JS *true* and *false* are boolean, all other values are NULL.

SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean') = false

#### json_date(column, field) returns TIMESTAMP

Any JS numeric value will be converted to a JS date, all other values are NULL. 
  
    SELECT id, json_date(data,'date') FROM things WHERE json_date(data,'date') <= NOW();

### Array Functions

Array functions return typed Arrays that can be used with any of the PG Array operators. Currently we only have the ability to handle arrays of ints but others are coming.

#### json_int_array(column, field)
         
Will wrap an integer into an array as required

SELECT id, (json_int_array(data,'object.list') FROM things WHERE 10 = ALL (json_int_array(data,'object.list'))
     
     SELECT id, (json_int_array(data,'object.list') FROM things WHERE 10 = ANY (json_int_array(data,'object.list'))

### Advanced Functions

Array functions can be chained together, allowing for multiple operations on JSON data within a single transaction:

UPDATE things SET data = json_push(json_add_to_set(data, 'array', '101'), 'array', '99');

#### json_push(column, field, json_value)

Appends json_value to an array or if the field is not present, will set the field to be an array containing json_value. Will throw an error if the field is not an array. *json_value* is a string representing a valid JSON representation.

UPDATE things SET data = json_push(data, 'array', '10');
  
  
#### json_add_to_set(column, field, json_value)

Appends json_value to an array if it is not in the array already or if the field is not present, will set the field to be an array containing json_value. Will throw an error if the field is not an array. *json_value* is a string representing a valid JSON representation.
         
    UPDATE things SET data = json_add_to_set(data, 'object.array', '10');

##### Accessing Data

#### json_data(column, fields)

Returns only the specified fields from the JSON data column.

SELECT id, json_data(data, 'uuid,name') FROM things;

##### Indexes and/or Indices
  
An index can be created using any of the Scalar and Array Functions. 
Creating an index makes performance on-par with regular PG columnn data
  
    CREATE INDEX name_in_json ON things (json_string(data,'name'));
     
  
#### Sample Data

All the above have been tested with randomly generated data in the form:

{
	    "uuid":"ba596c94-9e50-11e1-a50e-70cd60fffe0e",
	    "integer":10,
	    "string":"Blick",      
	    "date":"2012-05-11T15:42:15+10:00",
	    "boolean":true,
	    "numeric":99.9,
	    "object":{
	      "string":"Ullrich",
	      "array":[3428,7389,5166,5823,3566,6086,3087,7690,6374,4531,6019,9722,8793,6732,5264,9618,5843,6714,5160,4065,2102,4972,2778,6110,4357,4385,1296,7981,607,3104,4992,8207,7517,1932,8097,2626,5196,425,8803,4778,7814,5337,9467,200,3542,4001,5930,4646,7304,4033,4838,7539,648,7016,6377,7957,7411,4023,7105,3676,9195,2337,8259,9166,9972,4740,7705,5368,5815,2592,5569,4842,6577,3805,1473,8585,9371,8732,9491,3819,7517,3437,6342,3397,8603,5324,676,7922,813,9850,8032,9324,733,5436,2971,9878,1648,6248,2109,1422]
	    }
    }

postsql

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README