fb_json

Firebird UDR module to support JSON parsing and composing

★ 3 on GitHub

View on GitHub

Anyone may press Interesting, signed in or not. It helps others find this project.

Member rating

No ratings yet.

README

fb_json

Firebird UDR module to support JSON parsing and composing

Basis

JSON support is based on bundled Delphi System.JSON library (since Delphi 10).

Routines

Routines are assembled into package json. Pseudotype string marks any of string type char, varchar or blob sub_type text. All the routines can accept and return any string type.

procedure parse

pascal

procedure parse(
    json        string    -- JSON to be parsed
)returns(
    source_type smallint  -- source JSON entity type 
  , number      integer   -- order number of item (pair) started from 1
  , key         string    -- pair key / null for others
  , value_      string    -- item (pair) value 
  , value_type  smallint  -- item (pair) value JSON entity type
);

pascal

JSON entity types are:
 0 - not a JSON entity
 1 - null
 2 - bool
 3 - number
 4 - string
 5 - pair
 6 - object
 7 - array

This selective procedure returns a set of pairs or items, depending on the source type (object or array). For simple sources (null, bool, number, string) it returns single record.

procedure h_create

pascal

procedure h_create(
    json   string
)returns(
    handle bigint
);

This selective procedure creates inner JSON node by parsing json input parameter and returns its handle at single fetch iteration. json must be object or array or null. If null an empty object will be created. The handle can only be used by h_append, h_put and h_serialize functions during fetch iteration. Using handle out this context or with another target is unsafe.

function h_serialize

pascal

function h_serialize(
    handle bigint
)returns   string;

This function returns JSON text representation of inner node given by its handle. Have to be called exclusivly at handle node lifetime during h_create procedure single fetch.

function json_type

pascal

function json_type(
    json_type smallint
)returns      varchar(6);

Auxiliary PSQL function for viewing a string description of a type.

function encode

pascal

function encode(
    str  string
)returns string;

Converts string into JSON string. I.e. quotes it, escapes inner quotas etc.

function decode

pascal

function decode(
    str  string
)returns string;

Converts JSON string into common string. I.e. unquotes it and clears inner escapes.

function append

pascal

function append(
    json   string   -- JSON to be enhanced
  , key    string   -- new pair key 
  , value_ string   -- new pair (item) value
  , type_  smallint -- value type
)returns   string;  -- enhanced JSON

Appends a new pair or item to the JSON string. The json must be an object or an array or null or empty. If null or empty new object or array is created depending on key presents.

If value is not conform type it is ignored.

Note! You can use this function to add multiple pairs with the same key. The JSON standard does not require the key to be unique.

function h_append

pascal

function h_append(
    handle bigint   -- JSON node handle
  , key    string   -- new pair key 
  , value_ string   -- new pair (item) value
  , type_  smallint -- value type
)returns   bigint;  -- JSON node handle

This is handle analog of append function. Does nothing if handle is 0. Always returns null.

Note! Functions h_append and append have the same entry point in the binary module fb_json. The difference is in SQL definitions only.

function put

pascal

function put(
    json   string   -- JSON object to be modified
  , key    string   -- pair key 
  , value_ string   -- new pair value
  , type_  smallint -- value type
)returns   string;  -- modified JSON object

Updates JSON object pair or inserts new pair if not found. The json must be an object or null or empty. If null or empty new object is created. The key must not be null or empty.

If type does not match value (e.g. type=0 ) the pair is removed.

function h_put

pascal

function h_append(
    handle bigint   -- JSON node handle
  , key    string   -- pair key 
  , value_ string   -- new pair value
  , type_  smallint -- value type
)returns   bigint;  -- JSON node handle

This is handle analog of put function. Does nothing if handle is 0. Always returns null.

Note! Functions h_put and put have the same entry point in the binary module fb_json. The difference is in SQL definitions only.

function remove

pascal

function remove(
    json   string   -- JSON object to be modified
  , key    string   -- pair key 
)returns   string;  -- modified JSON object

Removes pair from JSON object if found.

function array_to_object

pascal

function array_to_object(
    json       string   -- JSON array to be converted
  , key_name   string   -- key field name 
  , value_name string   -- value field name
)returns       string;  -- JSON object

This function accepts array like

pascal

[
    { "name": "propertyName1", "value": "propertyValue1" }
  , { "name": "propertyName2", "value": "propertyValue2" }
]
  key_name = 'name', value_name = 'value'

and converts it into object like

pascal

{
    "propertyName1": "propertyValue2"
  , "propertyName2": "propertyValue2"
}

function object_to_array

pascal

function object_to_array(
    json       string   -- JSON array to be converted
  , key_name   string   -- key field name
  , value_name string   -- value field name
)returns       string;  -- JSON object

Parses any JSON object onto array like

pascal

[
    { "name": "propertyName1", "value": "propertyValue1" }
  , { "name": "propertyName2", "value": "propertyValue2" }
]
  key_name = 'name', value_name = 'value'

Limitations

This module provides simple JSON support without JPath. The solution may not be very convenient and fast but it is sufficient for most tasks.

Installation

Download a release package.
Copy fb_json.dll to %firebird%\plugins\udr where %firebird% is Firebird (>=3.0) server root directory. Make sure library module matches the Firebird bitness.
Get script fb_json.sql. Modify the script if you need another parameters/returns string types.
Connect to target database and execute the script.

Using

You can use binaries as you see fit.

If you get code or part of code please keep my name and a link here.

Examples

Parse JSON object:

pascal

select
      json.json_type( j.source_type ) as json_type
    , j.number
    , j.key
    , j.value_
    , json.json_type( j.value_type )  as value_type
  from
    json.parse( '
      {
          "null"   : null
        , "bool"   : true
        , "number" : 123
        , "string" : "123"
        , "object" : { "a" : "b" }
        , "array"  : [ 1, 2, 3 ]
      }
    ') j
;

pascal

JSON_TYPE  NUMBER  KEY      VALUE_    VALUE_TYPE
=========  ======  =======  =======   ==========
object          1  null     null      null
object          2  bool     true      bool
object          3  number   123       number
object          4  string   123       string
object          5  object   {"a":"b"} object
object          6  array    [1,2,3]   array

Note: strings returned dequoted (decoded).

Parse JSON array:

pascal

select
      json.json_type( j.source_type ) as json_type
    , j.number
    , j.key
    , j.value_
    , json.json_type( j.value_type )  as value_type
  from
    json.parse( '
      [
          null
        , true
        , 123
        , "123"
        , { "a" : "b" }
        , [ 1, 2, 3 ]
      ]
    ') j
;

pascal

JSON_TYPE  NUMBER  KEY      VALUE_    VALUE_TYPE
=========  ======  =======  =======   ==========
array           1  <null>   null      null
array           2  <null>   true      bool
array           3  <null>   123       number
array           4  <null>   123       string
array           5  <null>   {"a":"b"} object
array           6  <null>   [1,2,3]   array

Encode string for JSON:

pascal

select
    json.encode( '{ "a" : "b" }' )
  from
    rdb$database
;

pascal

ENCODE
====================
"{ \"a\" : \"b\" }"

Decode JSON string:

pascal

select
    json.decode( '"{ \"a\" : \"b\" }"' )
  from
    rdb$database
;

pascal

DECODE
====================
{ "a" : "b" }

Append pairs to JSON object:

pascal

select
    json.append(
        null
      , 'a'
      , 'b'
      , 4
    )
  from
    rdb$database
;

pascal

APPEND
==========
{"a":"b"}

pascal

select
    json.append(
        '{"a":"b"}'
      , 'x'
      , 'y'
      , 4
    )
  from
    rdb$database
;

pascal

APPEND
==================
{"a":"b","x":"y"}

Set pair value in JSON object:

pascal

select
    json.put(
        '{"a":"b","x":"y"}'
      , 'x'
      , 'z'
      , 4
    )
  from
    rdb$database
;

pascal

PUT
==================
{"a":"b","x":"z"}

Remove pair from JSON object:

pascal

select
    json.remove(
        '{"a":"b","x":"y"}'
      , 'x'
    )
  from
    rdb$database
;

pascal

REMOVE
==========
{"a":"b"}

Convert name/value array into object:

pascal

select
    json.array_to_object(
        '[ {"name":"a","value":"x"}, {"name":"b","value":"y"} ]'
      , 'name'
      , 'value'
    )
  from
    rdb$database
;

pascal

ARRAY_TO_OBJECT
=================
{"a":"x","b":"y"}

Convert object into name/value array:

pascal

select
    json.object_to_array(
        '{"a":"x","b":"y"}'
      , 'name'
      , 'value'
    )
  from
    rdb$database
;

pascal

OBJECT_TO_ARRAY
===================================================
[{"name":"a","value":"x"},{"name":"b","value":"y"}]

Make address JSON object by set of parameters

pascal

select
    coalesce(
        json.h_append( handle, 'zipcode',  :zipcode,  3 )
      , json.h_append( handle, 'region',   :region,   3 )
      , json.h_append( handle, 'city',     :city,     4 )
      , json.h_append( handle, 'street',   :street,   4 )
      , json.h_append( handle, 'building', :building, 4 )
      , json.h_serialize( handle )
    ) as address
  from
    json.h_create( null )

pascal

ADDRESS
===================================================
{"zipcode":666666,"region":77,"city":"Москва","street":"пр-т Ленинский","building":"15"}

Make JSON array book list from table BOOKS

pascal

create function make_book_list()
returns varchar(8191)
as
    declare variable result varchar(8191);
    declare variable h_list bigint;
    declare variable author varchar(100);
    declare variable title  varchar(100);
    declare variable book   varchar(100);
begin

pascal

for select
    handle
  from
    json.h_create( '[]' )
  into
    :h_list
do begin

pascal

for select
      author
    , title
  from
    books
  into
      :author
    , :title
do begin

pascal

:book = '{}';
:book = json.append( :book, 'author', author, 4 );
:book = json.append( :book, 'title',  title,  4 );

pascal

json.h_append( :h_list, null, :book, 6 );

pascal

end

pascal

:result = json.h_serialize( h_list );

pascal

end

pascal

return :result;

pascal

end

pascal

select
    make_book_list() as booklist
  from
    rdb$database

pascal

BOOKLIST
===================================================
[
  {
    "author" : "Helen Borrie",
    "title" : "The Firebird Book: A Reference for Database Developers"
  },
  {
    "author" : "Ковязин Алексей Н., Востриков Сергей М.",
    "title" : "Мир InterBase"
  },
  {
    "author" : "Denis Simonov",
    "title" : "Firebird UDR writing in Pascal"
  }
]

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Ffb_json

Member rating

README

fb_json

Basis

Routines

procedure parse

procedure h_create

function h_serialize

function json_type

function encode

function decode

function append

function h_append

function put

function h_put

function remove

function array_to_object

function object_to_array

Limitations

Installation

Using

Examples

fb_json