JSON_INGEST ​

Convert a JSON input string into a Document Object Model (DOM) representation of the string that you can query with the JSON_LOOKUP function.

JSON_INGEST(json_input_string)

where json_input_string is a JSON document that Yellowbrick stores as a VARCHAR data type.

Usage Notes ​

• Important: Running JSON_INGEST as a first step is only required when you plan to run JSON_LOOKUP with the jpath option. If you run JSON_LOOKUP repeatedly on the same data, performance will improve considerably on repeated queries because the jpath option uses a hash index optimization. The other path types do not have this capability.

If you use the jpointer or jpointer_simdjson path type when you run queries with the JSON_LOOKUP function, you do not need to run JSON_INGEST on the JSON input first; you can use JSON_LOOKUP to query the data directly.

• When you use the JSON_INGEST function, Yellowbrick recommends that you store the original JSON data as well as the function results. A new version of the function in a later release may require the generation of new hash values.
• The JSON_INGEST function returns a VARCHAR data type.
• The size of the output DOM string may be 2 to 3 three times larger than the original JSON input string. The maximum ingested output cannot be greater than 64000 bytes (the maximum length of a VARCHAR column), so the input string must be significantly smaller.
• The JSON_INGEST function supports JSON strings with a maximum depth of 250 levels.
• The ybload client can load JSON documents in text or csv format.

Example ​

See also the examples for JSON_LOOKUP.

First, create a table that stores the JSON data:

yellowbrick=# create table json_movies (jsonstr varchar(1000));
CREATE TABLE
yellowbrick=# insert into json_movies values('{"title":"After Dark in Central Park","year":1900,"cast":[],"genres":[]}');
INSERT 0 1
yellowbrick=# select * from json_movies;
                                jsonstr                                  
--------------------------------------------------------------------------
 {"title":"After Dark in Central Park","year":1900,"cast":[],"genres":[]}
(1 row)

Now create a table that can store the JSON data alongside the results of the JSON_INGEST function:

yellowbrick=# create table json_movies_ingested as select jsonstr, json_ingest(jsonstr) from json_movies;
SELECT 1
yellowbrick=# \d json_movies_ingested
       Table "public.json_movies_ingested"
   Column    |           Type           | Modifiers 
-------------+--------------------------+-----------
 jsonstr     | character varying(1000)  | 
 json_ingest | character varying(64000) | 

Distribution: Hash (jsonstr)

Now you can write queries with the JSON_LOOKUP function:

yellowbrick=# select json_lookup(json_ingest,'$.title','jpath') from json_movies_ingested;
       json_lookup         
----------------------------
 After Dark in Central Park
(1 row)
yellowbrick=# select json_lookup(json_ingest,'$.year','jpath') from json_movies_ingested;
 json_lookup 
-------------
 1900
(1 row)

Parent topic:JSON Functions