Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

JSON_LOOKUP ​

Tip: This function was added in Version 5.4.10 and is not available in earlier 5.4.x versions.

Return the key value for a JSON object.

JSON_LOOKUP(column_name, path_to_json_key, path_type)

Parameters ​

column_name

A table column that stores the DOM representation of the JSON input (as a result of running JSON_INGEST on the input).

path_to_json_key

String that represents the JSON key for which you want to return the corresponding value. Supported operators depend on the path type.

  • jpath: $, ., [] (root is '$'). For example:
    • $.year
    • $.year.month
  • jpointer and jpointer_simdjson: /, /[array index], /[object key] (root is the empty string '', not '/'). For example:
    • /year
    • /year/0
    • /year/month
path_type

One of the following types must be specified:

  • jpath: the RapidJSON library is used in the query.

    Important: Running JSON_INGEST as a first step is always 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 because the jpath option uses a hash index optimization. The other path types do not have this capability.

  • jpointer: the RapidJSON library is used in the query.

    Important: If you use the jpointer or jpointer_simdjson path type, you do not need to run JSON_INGEST on the JSON input first; you can use JSON_LOOKUP directly on the original JSON data.

  • jpointer_simdjson: the SIMDJSON library is used in the query.

Usage Notes ​

  • The function returns a VARCHAR data type.
  • Numeric data is supported as text, and its representation is preserved (for example, 1.02, 102e-2).
  • The function returns true and false for Boolean values.
  • The function returns null when it encounters empty results, empty JSON documents, empty strings, or an empty jpath.
  • The function returns "null" as a string for cases like this: {"a": null} with path $.a
  • The function returns the PostgreSQL NULL value for missing values: {"b": 10} with path $.a
  • A single string or number is valid JSON. This function supports inputs where the root of a JSON document is a scalar value only, not a key-value object. For example, the whole document may be the value 2018-2019, instead of, for example, {"Season Name": 2018-2019}.
  • The function returns errors when any JSON input is malformed. Any error in any row will cause the query to fail. For example:
ERROR: json_lookup: malformed input, reason: Missing a closing quotation mark in string. pos: 14/14 input: "Hello, world!
  • Errors for queries that use the jpointer_simdjson path type do not return the position of the failure in the message.

Examples ​

The following examples operate on simple JSON strings that look like this:

premdb=# select "25 Seasons" from seasons_json;
                     25 Seasons                      
------------------------------------------------------
 {"Season Number" : 1, "Season Years" : "1992-1993"}
 {"Season Number" : 2, "Season Years" : "1993-1994"}
 {"Season Number" : 3, "Season Years" : "1994-1995"}
 {"Season Number" : 4, "Season Years" : "1995-1996"}
 {"Season Number" : 5, "Season Years" : "1996-1997"}
...

First, create a table that applies the JSON_INGEST function to the original JSON strings:

premdb=# create table seasons_ingest as select "25 Seasons", json_ingest("25 Seasons") from seasons_json;
SELECT 25
premdb=# \d seasons_ingest
          Table "public.seasons_ingest"
   Column    |           Type           | Modifiers 
-------------+--------------------------+-----------
 25 Seasons  | character varying(116)   | 
 json_ingest | character varying(64000) | 

Distribution: Replicated

Now query the ingested JSON column, using the jpath type:

premdb=# select json_lookup(json_ingest,'$.Season Years','jpath') from seasons_ingest;
 json_lookup 
-------------
 1992-1993
 1993-1994
 1994-1995
 1995-1996
 1996-1997
...

Now query with the jpointer type, which means you can query the original seasons_json table, not the table with the ingested strings:

premdb=# select json_lookup("25 Seasons",'/Season Number','jpointer')::int 
from seasons_json order by 1 desc limit 5;
 json_lookup 
-------------
         25
         24
         23
         22
         21
(5 rows)

Note that this function always returns a VARCHAR string. To order by a string that represents a number, use an explicit cast as shown in the previous query. Without the cast, the same query orders by character strings and returns:

premdb=# select json_lookup("25 Seasons",'/Season Number','jpointer') 
from seasons_json order by 1 desc limit 5;
 json_lookup 
-------------
 9
 8
 7
 6
 5
(5 rows)

The following example shows how to query JSON strings that have nested data. Create a table and insert one row of nested JSON data:

yellowbrick=# create table json_gloss(entry varchar(1000);
yellowbrick(# 
yellowbrick=# create table json_gloss(entry varchar(1000));
CREATE TABLE
yellowbrick=# insert into json_gloss(entry) values('{
yellowbrick'#     "glossary": {
yellowbrick'#         "title": "example glossary",
yellowbrick'#         "GlossDiv": {
yellowbrick'#             "title": "S",
yellowbrick'#             "GlossList": {
yellowbrick'#                 "GlossEntry": {
yellowbrick'#                     "ID": "SGML",
yellowbrick'#                     "SortAs": "SGML",
yellowbrick'#                     "GlossTerm": "Standard Generalized Markup Language",
yellowbrick'#                     "Acronym": "SGML",
yellowbrick'#                     "Abbrev": "ISO 8879:1986",
yellowbrick'#                     "GlossDef": {
yellowbrick'#                         "para": "A meta-markup language, used to create markup languages such as DocBook.",
yellowbrick'#                         "GlossSeeAlso": [
yellowbrick'#                             "GML",
yellowbrick'#                             "XML"
yellowbrick'#                         ]
yellowbrick'#                     },
yellowbrick'#                     "GlossSee": "markup"
yellowbrick'#                 }
yellowbrick'#             }
yellowbrick'#         }
yellowbrick'#     }
yellowbrick'# }');
INSERT 0 1

Now create a table that holds both the original data and the output for the JSON_INGEST function:

yellowbrick=# create table json_gloss_ingested as select entry, json_ingest(entry) from json_gloss;
SELECT 1

Now run queries on the json_gloss_ingested table:

yellowbrick=# select json_lookup(json_ingest,'$.glossary.title','jpath') from json_gloss_ingested;
   json_lookup    
------------------
 example glossary
(1 row)
yellowbrick=# select json_lookup(json_ingest,'$.glossary.GlossDiv.GlossList.GlossEntry.Acronym','jpath') from json_gloss_ingested;
 json_lookup 
-------------
 SGML
(1 row)

The following example shows support for JSON strings that consist only of single scalar values. Note that the root for jpointer is specified with the empty string '':

premdb=# select * from scalar_json;
    col1      
---------------
 "2018-2019"
 "Twenty-five"
 "25"
(3 rows)

premdb=# select json_lookup(col1,'','jpointer') from scalar_json;
 json_lookup 
-------------
 2018-2019
 Twenty-five
 25
(3 rows)

Parent topic:JSON Functions