edit-icon download-icon

Operations of JSON data

Last Updated: Dec 28, 2017

The JSON type has become the standard data type of the Internet and the Internet of Things (IoT). You can view the specific protocols at the JSON Official Website. PostgreSQL supports JSON well. HybridDB for PostgreSQL also supports the JSON data type based on the PostgreSQL syntax.

This document introduces the basic operations and supported objects of the JSON data in HybridDB for PostgreSQL, including checking compatibility, converting strings to JSON, internal data types, operators, and functions. In addition, some usage examples are provided for your reference.

Check whether the current version supports JSON

Start a HybridDB for PostgreSQL instance, and run the following command to check whether the current version supports JSON or not:

  1. => SELECT '""'::json;

If the operation fails, restart the instance and run the preceding command again.

This command dictates a force type conversion from a string to the JSON format, and the following results indicates whether the JSON type is supported.

  • If the system prompts the following response, it indicates that the JSON type is supported and the instance is ready for use.

    1. json
    2. ------
    3. ""
    4. (1 row)
  • If the system prompts the following response, it indicates that the JSON type is not supported yet.

    1. ERROR: type "json" does not exist
    2. LINE 1: SELECT '""'::json;
    3. ^

JSON conversion in the database

Database operations mainly involve: read and write. Writing JSON data means converting strings to JSON format. The content in the strings must conform to the JSON standard, including strings, numbers, arrays, and objects. For example:

String

  1. => SELECT '"hijson"'::json;
  2. json
  3. -------
  4. "hijson"
  5. (1 row)

:: represents force type conversion in PostgreSQL, Greenplum and HybridDB for PostgreSQL. The JSON type input function is called during the conversion process. Therefore, JSON format check is performed during the type conversion as follows:

  1. => SELECT '{hijson:1024}'::json;
  2. ERROR: invalid input syntax for type json
  3. LINE 1: SELECT '{hijson:1024}'::json;
  4. ^
  5. DETAIL: Token "hijson" is invalid.
  6. CONTEXT: JSON data, line 1: {hijson...
  7. =>

The aforementioned " are necessary for "hijson". Because the JSON standard requires the KEY value to be a string, the {hijson:1024} here returns a syntax error.

Apart from the type conversion, the conversion from the database record to the JSON string is also performed.

We do not normally use only one string or one number for JSON, but an object that contains one or more key-value pairs. So for Greenplum, conversion to objects is applicable to a majority of JSON scenarios, such as:

  1. => select row_to_json(row('{"a":"a"}', 'b'));
  2. row_to_json
  3. ---------------------------------
  4. {"f1":"{\"a\":\"a\"}","f2":"b"}
  5. (1 row)
  6. => select row_to_json(row('{"a":"a"}'::json, 'b'));
  7. row_to_json
  8. ---------------------------
  9. {"f1":{"a":"a"},"f2":"b"}
  10. (1 row)

We can also see the differences between the string and JSON here, so as to conveniently convert a full record into the JSON type.

JSON internal data types

  • Object

    The object is the most frequently used data in JSON, such as:

    1. => select '{"key":"value"}'::json;
    2. json
    3. -----------------
    4. {"key":"value"}
    5. (1 row)
  • Integer and floating point

    The JSON protocol only has three types of numbers: integer, floating point and constant expression. Greenplum provides good support for all three number types.

    1. => SELECT '1024'::json;
    2. json
    3. ------
    4. 1024
    5. (1 row)
    6. => SELECT '0.1'::json;
    7. json
    8. ------
    9. 0.1
    10. (1 row)

    The following information is required in some special situations:

    1. => SELECT '1e100'::json;
    2. json
    3. -------
    4. 1e100
    5. (1 row)
    6. => SELECT '{"f":1e100}'::json;
    7. json
    8. -------------
    9. {"f":1e100}
    10. (1 row)

    And the extra-long number is also included as follows:

    1. => SELECT '9223372036854775808'::json;
    2. json
    3. ---------------------
    4. 9223372036854775808
    5. (1 row)
  • Array

    1. => SELECT '[[1,2], [3,4,5]]'::json;
    2. json
    3. ------------------
    4. [[1,2], [3,4,5]]
    5. (1 row)

Operators

Operator types supported by JSON

  1. => select oprname,oprcode from pg_operator where oprleft = 3114;
  2. oprname | oprcode
  3. ---------+---------------------------
  4. -> | json_object_field
  5. ->> | json_object_field_text
  6. -> | json_array_element
  7. ->> | json_array_element_text
  8. #> | json_extract_path_op
  9. #>> | json_extract_path_text_op
  10. (6 rows)

Basic usage

  1. => SELECT '{"f":"1e100"}'::json -> 'f';
  2. ?column?
  3. ----------
  4. "1e100"
  5. (1 row)
  6. => SELECT '{"f":"1e100"}'::json ->> 'f';
  7. ?column?
  8. ----------
  9. 1e100
  10. (1 row)
  11. => select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6'];
  12. ?column?
  13. -----------
  14. "stringy"
  15. (1 row)
  16. => select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f4,f6}';
  17. ?column?
  18. -----------
  19. "stringy"
  20. (1 row)
  21. => select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,0}';
  22. ?column?
  23. ----------
  24. f3
  25. (1 row)

JSON functions

Supported functions

  1. postgres=# \df *json*
  2. List of functions
  3. Schema | Name | Result data type | Argument data types | Type
  4. ------------+---------------------------+------------------+-----------------------------------------------------------+--------
  5. pg_catalog | array_to_json | json | anyarray | normal
  6. pg_catalog | array_to_json | json | anyarray, boolean | normal
  7. pg_catalog | json_array_element | json | from_json json, element_index integer | normal
  8. pg_catalog | json_array_element_text | text | from_json json, element_index integer | normal
  9. pg_catalog | json_array_elements | SETOF json | from_json json, OUT value json | normal
  10. pg_catalog | json_array_length | integer | json | normal
  11. pg_catalog | json_each | SETOF record | from_json json, OUT key text, OUT value json | normal
  12. pg_catalog | json_each_text | SETOF record | from_json json, OUT key text, OUT value text | normal
  13. pg_catalog | json_extract_path | json | from_json json, VARIADIC path_elems text[] | normal
  14. pg_catalog | json_extract_path_op | json | from_json json, path_elems text[] | normal
  15. pg_catalog | json_extract_path_text | text | from_json json, VARIADIC path_elems text[] | normal
  16. pg_catalog | json_extract_path_text_op | text | from_json json, path_elems text[] | normal
  17. pg_catalog | json_in | json | cstring | normal
  18. pg_catalog | json_object_field | json | from_json json, field_name text | normal
  19. pg_catalog | json_object_field_text | text | from_json json, field_name text | normal
  20. pg_catalog | json_object_keys | SETOF text | json | normal
  21. pg_catalog | json_out | cstring | json | normal
  22. pg_catalog | json_populate_record | anyelement | base anyelement, from_json json, use_json_as_text boolean | normal
  23. pg_catalog | json_populate_recordset | SETOF anyelement | base anyelement, from_json json, use_json_as_text boolean | normal
  24. pg_catalog | json_recv | json | internal | normal
  25. pg_catalog | json_send | bytea | json | normal
  26. pg_catalog | row_to_json | json | record | normal
  27. pg_catalog | row_to_json | json | record, boolean | normal
  28. pg_catalog | to_json | json | anyelement | normal
  29. (24 rows)

Basic usage

  1. => SELECT array_to_json('{{1,5},{99,100}}'::int[]);
  2. array_to_json
  3. ------------------
  4. [[1,5],[99,100]]
  5. (1 row)
  6. => SELECT row_to_json(row(1,'foo'));
  7. row_to_json
  8. ---------------------
  9. {"f1":1,"f2":"foo"}
  10. (1 row)
  11. => SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
  12. json_array_length
  13. -------------------
  14. 5
  15. (1 row)
  16. => select * from json_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q;
  17. key | value
  18. -----+-----------
  19. f1 | [1,2,3]
  20. f2 | {"f3":1}
  21. f4 | null
  22. f5 | 99
  23. f6 | "stringy"
  24. (5 rows)
  25. => select json_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":"null"}');
  26. json_each_text
  27. -------------------
  28. (f1,"[1,2,3]")
  29. (f2,"{""f3"":1}")
  30. (f4,)
  31. (f5,null)
  32. (4 rows)
  33. => select json_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
  34. json_array_elements
  35. -----------------------
  36. 1
  37. true
  38. [1,[2,3]]
  39. null
  40. {"f1":1,"f2":[7,8,9]}
  41. false
  42. (6 rows)
  43. create type jpop as (a text, b int, c timestamp);
  44. => select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', false) q;
  45. a | b | c
  46. --------+---+---
  47. blurfl | |
  48. (1 row)
  49. => select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q;
  50. a | b | c
  51. --------+---+--------------------------
  52. blurfl | |
  53. | 3 | Fri Jan 20 10:42:53 2012
  54. (2 rows)

Code cases

Create a table

  1. create table tj(id serial, ary int[], obj json, num integer);
  2. => insert into tj(ary, obj, num) values('{1,5}'::int[], '{"obj":1}', 5);
  3. INSERT 0 1
  4. => select row_to_json(q) from (select id, ary, obj, num from tj) as q;
  5. row_to_json
  6. -------------------------------------------
  7. {"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
  8. (1 row)
  9. => insert into tj(ary, obj, num) values('{2,5}'::int[], '{"obj":2}', 5);
  10. INSERT 0 1
  11. => select row_to_json(q) from (select id, ary, obj, num from tj) as q;
  12. row_to_json
  13. -------------------------------------------
  14. {"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
  15. {"f1":2,"f2":[2,5],"f3":{"obj":2},"f4":5}
  16. (2 rows)

Multi-table JOIN

  1. create table tj2(id serial, ary int[], obj json, num integer);
  2. => insert into tj2(ary, obj, num) values('{2,5}'::int[], '{"obj":2}', 5);
  3. INSERT 0 1
  4. => select * from tj, tj2 where tj.obj->>'obj' = tj2.obj->>'obj';
  5. id | ary | obj | num | id | ary | obj | num
  6. ----+-------+-----------+-----+----+-------+-----------+-----
  7. 2 | {2,5} | {"obj":2} | 5 | 1 | {2,5} | {"obj":2} | 5
  8. (1 row)
  9. => select * from tj, tj2 where json_object_field_text(tj.obj, 'obj') = json_object_field_text(tj2.obj, 'obj');
  10. id | ary | obj | num | id | ary | obj | num
  11. ----+-------+-----------+-----+----+-------+-----------+-----
  12. 2 | {2,5} | {"obj":2} | 5 | 1 | {2,5} | {"obj":2} | 5
  13. (1 row)

JSON function indexing

  1. CREATE TEMP TABLE test_json (
  2. json_type text,
  3. obj json
  4. );
  5. => insert into test_json values('aa', '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}');
  6. INSERT 0 1
  7. => insert into test_json values('cc', '{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');
  8. INSERT 0 1
  9. => select obj->'f2' from test_json where json_type = 'aa';
  10. ?column?
  11. ----------
  12. {"f3":1}
  13. (1 row)
  14. => create index i on test_json (json_extract_path_text(obj, '{f4}'));
  15. CREATE INDEX
  16. => select * from test_json where json_extract_path_text(obj, '{f4}') = '{"f5":99,"f6":"foo"}';
  17. json_type | obj
  18. -----------+-------------------------------------------
  19. aa | {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}
  20. (1 row)

Note: JSON type cannot be used as the distribution key for now and the JSON aggregate functions are not supported.

The following is an example of Python access:

  1. #! /bin/env python
  2. import time
  3. import json
  4. import psycopg2
  5. def gpquery(sql):
  6. conn = None
  7. try:
  8. conn = psycopg2.connect("dbname=sanity1x2")
  9. conn.autocommit = True
  10. cur = conn.cursor()
  11. cur.execute(sql)
  12. return cur.fetchall()
  13. except Exception as e:
  14. if conn:
  15. try:
  16. conn.close()
  17. except:
  18. pass
  19. time.sleep(10)
  20. print e
  21. return None
  22. def main():
  23. sql = "select obj from tj;"
  24. #rows = Connection(host, port, user, pwd, dbname).query(sql)
  25. rows = gpquery(sql)
  26. for row in rows:
  27. print json.loads(row[0])
  28. if __name__ == "__main__":
  29. main()
Thank you! We've received your feedback.