SELECT parse_json_path('..');
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path('..*');
 parse_json_path 
-----------------
 $..[*]
(1 row)

SELECT parse_json_path('.*');
 parse_json_path 
-----------------
 $[*]
(1 row)

SELECT parse_json_path('');
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path('$.*');
 parse_json_path 
-----------------
 $[*]
(1 row)

SELECT parse_json_path('$');
 parse_json_path 
-----------------
 $
(1 row)

SELECT parse_json_path('$*');
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path('*');
 parse_json_path 
-----------------
 $[*]
(1 row)

SELECT parse_json_path($$ .. [ 025  ] $$);
 parse_json_path 
-----------------
 $..[25]
(1 row)

SELECT parse_json_path($$ $ . 0 . 3 $$);
 parse_json_path 
-----------------
 $[0][3]
(1 row)

SELECT parse_json_path($$ $ . 0_3 $$);
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path($$ $ . 0.3 $$);
 parse_json_path 
-----------------
 $[0][3]
(1 row)

SELECT parse_json_path($$ .. [ 031  ] $$);
 parse_json_path 
-----------------
 $..[31]
(1 row)

SELECT parse_json_path($$ $ . 0 . 3 . hello $$);
 parse_json_path  
------------------
 $[0][3]["hello"]
(1 row)

SELECT parse_json_path($$ $ . 0 . 3 . hello . "back\\slash" $$);
         parse_json_path         
---------------------------------
 $[0][3]["hello"]["back\\slash"]
(1 row)

SELECT parse_json_path($$ $ . 0 . 3 . hello . ["back\\slash"] $$);
         parse_json_path         
---------------------------------
 $[0][3]["hello"]["back\\slash"]
(1 row)

SELECT parse_json_path($$ $ . 0 . 3 . hello . back\slash $$);
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path($$ $ . 0 . 3 . hello . backslash $$);
        parse_json_path        
-------------------------------
 $[0][3]["hello"]["backslash"]
(1 row)

SELECT parse_json_path($$ .. [ 0x31  ] $$);
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path($$ [ 0x31  ] $$);
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path($$ _3 $$);
 parse_json_path 
-----------------
 $["_3"]
(1 row)

SELECT parse_json_path($$ _3_ $$);
 parse_json_path 
-----------------
 $["_3_"]
(1 row)

SELECT parse_json_path($$ [ _3 ] $$);
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path($$ $ _3 $$);
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path($$ $ . _3 $$);
 parse_json_path 
-----------------
 $["_3"]
(1 row)

SELECT parse_json_path('..["5"]');
 parse_json_path 
-----------------
 $..["5"]
(1 row)

SELECT parse_json_path('..[5]');
 parse_json_path 
-----------------
 $..[5]
(1 row)

SELECT parse_json_path('..5');
 parse_json_path 
-----------------
 $..[5]
(1 row)

SELECT parse_json_path($$ .. [ -5  ] $$);
 parse_json_path 
-----------------
 $..[-5]
(1 row)

SELECT parse_json_path($$ .. [ "5"  ] $$);
 parse_json_path 
-----------------
 $..["5"]
(1 row)

SELECT parse_json_path($$ .. [ +5  ] $$);
 parse_json_path 
-----------------
 $..[5]
(1 row)

SELECT parse_json_path($$ .. [ 5  ] $$);
 parse_json_path 
-----------------
 $..[5]
(1 row)

SELECT parse_json_path($$ .. ["5"] $$);
 parse_json_path 
-----------------
 $..["5"]
(1 row)

SELECT parse_json_path($$ ..["5"] $$);
 parse_json_path 
-----------------
 $..["5"]
(1 row)

SELECT parse_json_path($$ [ "5" ] $$);
 parse_json_path 
-----------------
 $["5"]
(1 row)

SELECT parse_json_path($$ [ 5 ] $$);
 parse_json_path 
-----------------
 $[5]
(1 row)

SELECT parse_json_path('."hello"');
 parse_json_path 
-----------------
 $["hello"]
(1 row)

SELECT parse_json_path('.hello');
 parse_json_path 
-----------------
 $["hello"]
(1 row)

SELECT parse_json_path('...["hello world"]');
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path('..["hello world"]');
  parse_json_path   
--------------------
 $..["hello world"]
(1 row)

SELECT parse_json_path('."hello world"');
 parse_json_path  
------------------
 $["hello world"]
(1 row)

SELECT parse_json_path('.["hello world"]');
 parse_json_path  
------------------
 $["hello world"]
(1 row)

SELECT parse_json_path('.hello world');
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path('..["hello world"]["5"]');
     parse_json_path     
-------------------------
 $..["hello world"]["5"]
(1 row)

SELECT parse_json_path('..["hello world"][5]');
    parse_json_path    
-----------------------
 $..["hello world"][5]
(1 row)

SELECT parse_json_path('..["hello world"][5]..[3]..*.[*]');
          parse_json_path           
------------------------------------
 $..["hello world"][5]..[3]..[*][*]
(1 row)

SELECT parse_json_path('..["hello world"][5]..[3]..*');
         parse_json_path         
---------------------------------
 $..["hello world"][5]..[3]..[*]
(1 row)

SELECT parse_json_path('..["hello world"][5]..[3]');
      parse_json_path       
----------------------------
 $..["hello world"][5]..[3]
(1 row)

SELECT parse_json_path('..["hello world"][5]..[3]*');
 parse_json_path 
-----------------
 
(1 row)

SELECT parse_json_path($$
	.. [ '"hello"\\"world"' ]
	[ 5 ] .. [3] . * [ * ]
$$);
              parse_json_path              
-------------------------------------------
 $..["\"hello\"\\\"world\""][5]..[3][*][*]
(1 row)

select parse_json_path('$char');
 parse_json_path 
-----------------
 
(1 row)

select parse_json_path('$.char');
 parse_json_path 
-----------------
 $["char"]
(1 row)

select parse_json_path('$.char()');
 parse_json_path 
-----------------
 
(1 row)

select parse_json_path('$.char(5)');
 parse_json_path 
-----------------
 $.char(5)
(1 row)

select parse_json_path('$.char(  -1  )');
 parse_json_path 
-----------------
 $.char(-1)
(1 row)

select parse_json_path('$.char(  -1  )  . char(0)');
  parse_json_path   
--------------------
 $.char(-1).char(0)
(1 row)

select parse_json_path('$.char(  -1  )  . char  ( 0 )  ');
  parse_json_path   
--------------------
 $.char(-1).char(0)
(1 row)

select parse_json_path('$.char(  +1  )  . char  ( 0 )  ');
  parse_json_path  
-------------------
 $.char(1).char(0)
(1 row)

select parse_json_path('$ . char(  +1  )  . char  ( 0 )  ');
  parse_json_path  
-------------------
 $.char(1).char(0)
(1 row)

select parse_json_path('$ .. char(  +1  )  . char  ( 0 )  ');
  parse_json_path   
--------------------
 $..char(1).char(0)
(1 row)

select parse_json_path('$ .. char(  +1  )  .. char  ( 0 )  ');
   parse_json_path   
---------------------
 $..char(1)..char(0)
(1 row)

select parse_json_path('$ . char(  +1  )  .. char  ( 0 )  ');
  parse_json_path   
--------------------
 $.char(1)..char(0)
(1 row)

select parse_json_path('$ . char(  +1  )  char  ( 0 )  ');
 parse_json_path 
-----------------
 
(1 row)

select parse_json_path('$ char(  +1  )  char  ( 0 )  ');
 parse_json_path 
-----------------
 
(1 row)

select parse_json_path('char(  +1  )  . char  ( 0 )  ');
  parse_json_path  
-------------------
 $.char(1).char(0)
(1 row)

select parse_json_path('.char(  +1  )  . char  ( 0 )  ');
  parse_json_path  
-------------------
 $.char(1).char(0)
(1 row)

select parse_json_path('..char(  +1  )  . char  ( 0 )  ');
  parse_json_path   
--------------------
 $..char(1).char(0)
(1 row)

select parse_json_path('...char(  +1  )  . char  ( 0 )  ');
 parse_json_path 
-----------------
 
(1 row)

select parse_json_path('[char(5)]');
 parse_json_path 
-----------------
 
(1 row)

SELECT json_path($$"⁰¹\u0000³"$$, 'char(0)');
 json_path 
-----------
 "⁰"
(1 row)

SELECT json_path($$"⁰¹\u0000³"$$, 'char(1)');
 json_path 
-----------
 "¹"
(1 row)

SELECT json_path($$"⁰¹\u0000³"$$, 'char(2)');
 json_path 
-----------
 "\u0000"
(1 row)

SELECT json_path($$"⁰¹\u0000³"$$, 'char(3)');
 json_path 
-----------
 "³"
(1 row)

SELECT json_path('"0123"', 'char(-1)');
 json_path 
-----------
(0 rows)

SELECT json_path('"0123"', 'char(0)');
 json_path 
-----------
 "0"
(1 row)

SELECT json_path('"0123"', 'char(1)');
 json_path 
-----------
 "1"
(1 row)

SELECT json_path('"0123"', 'char(2)');
 json_path 
-----------
 "2"
(1 row)

SELECT json_path('"0123"', 'char(3)');
 json_path 
-----------
 "3"
(1 row)

SELECT json_path('"0123"', 'char(4)');
 json_path 
-----------
(0 rows)

SELECT json_path('"⁰¹²³"', 'char(0)');
 json_path 
-----------
 "⁰"
(1 row)

SELECT json_path('"⁰¹²³"', 'char(1)');
 json_path 
-----------
 "¹"
(1 row)

SELECT json_path('"⁰¹²³"', 'char(2)');
 json_path 
-----------
 "²"
(1 row)

SELECT json_path('"⁰¹²³"', 'char(3)');
 json_path 
-----------
 "³"
(1 row)

SELECT json_path('[1,2,3]', '$');
 json_path 
-----------
 [1,2,3]
(1 row)

SELECT json_path('[1,2,3]', '$.+1');
 json_path 
-----------
 2
(1 row)

SELECT json_path('[1,2,3]', '$.-1');
 json_path 
-----------
(0 rows)

SELECT json_path('[1,2,3]', '$.0');
 json_path 
-----------
 1
(1 row)

SELECT json_path('[1,2,3]', '$.1');
 json_path 
-----------
 2
(1 row)

SELECT json_path('[1,2,3]', '$.2');
 json_path 
-----------
 3
(1 row)

SELECT json_path('[1,2,3]', '$.3');
 json_path 
-----------
(0 rows)

SELECT json_path('[1,2,3]', '*');
 json_path 
-----------
 1
 2
 3
(3 rows)

SELECT json_path('[1,2,3]', '[0]');
 json_path 
-----------
 1
(1 row)

SELECT json_path('[1,2,3]', '[1]');
 json_path 
-----------
 2
(1 row)

SELECT json_path('[1,2,3]', '[2]');
 json_path 
-----------
 3
(1 row)

SELECT json_path('{"0": "zero", "1": "one", "2": "two"}', $$['2']$$);
 json_path 
-----------
 "two"
(1 row)

SELECT json_path('{"0": "zero", "1": "one", "2": "two"}', '["0"]');
 json_path 
-----------
 "zero"
(1 row)

SELECT json_path('{"0": "zero", "1": "one", "2": "two"}', '["1"]');
 json_path 
-----------
 "one"
(1 row)

SELECT json_path('{"0": "zero", "1": "one", "2": "two"}', '["2"]');
 json_path 
-----------
 "two"
(1 row)

SELECT json_path('{"0": "zero", "1": "one", "2": "two"}', '[0]');
 json_path 
-----------
(0 rows)

SELECT json_path('{"0": "zero", "1": "one", "2": "two"}', '[1]');
 json_path 
-----------
(0 rows)

-- Multiple JSONPath results are listed in BFS order
-- (just because it's easier to implement efficiently than DFS)
SELECT json_path('[0,1,[2, [3,4 , 5],6,[7,8],9],10]', '$..*');
        json_path         
--------------------------
 0
 1
 [2, [3,4 , 5],6,[7,8],9]
 10
 2
 [3,4 , 5]
 6
 [7,8]
 9
 3
 4
 5
 7
 8
(14 rows)

CREATE TABLE sample (json JSON);
INSERT INTO sample VALUES ($$
{
	"store": {
		"books": [
			{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}, {
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}, {
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
		],
		"toys": [
			"Yo-yo",
			"Boomerang",
			"Basketball"
		]
	}
}
$$);
-- Use unaligned output so results are consistent between PostgreSQL 8 and 9.
\a
SELECT json_path(json, 'store.books[*]') FROM sample;
json_path
{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}
{
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}
{
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
(3 rows)
SELECT json_path(json, 'store.books[*].title') FROM sample;
json_path
"book 0"
"book 1"
"book 2"
(3 rows)
SELECT json_path(json, 'store.books[*].author') FROM sample;
json_path
"author 0"
"author 1"
"author 2"
(3 rows)
SELECT json_path(json, 'store.books[*].prices') FROM sample;
json_path
[1,2,3]
[4,5,6]
[7,8,9]
(3 rows)
SELECT json_path(json, 'store.books[*].prices[*]') FROM sample;
json_path
1
2
3
4
5
6
7
8
9
(9 rows)
SELECT json_path(json, 'store.toys[*]') FROM sample;
json_path
"Yo-yo"
"Boomerang"
"Basketball"
(3 rows)
SELECT json_path(json, 'store.toys[*][0]') FROM sample;
json_path
(0 rows)
SELECT json_path(json, 'store.toys[*][1]') FROM sample;
json_path
(0 rows)
SELECT json_path(json, 'store.toys[*][0][0]') FROM sample;
json_path
(0 rows)
SELECT json_path(json, 'store.toys[*][0][1]') FROM sample;
json_path
(0 rows)
SELECT json_path(json, '..books') FROM sample;
json_path
[
			{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}, {
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}, {
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
		]
(1 row)
SELECT json_path(json, '..books[*]') FROM sample;
json_path
{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}
{
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}
{
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
(3 rows)
SELECT json_path(json, '..title') FROM sample;
json_path
"book 0"
"book 1"
"book 2"
(3 rows)
SELECT json_path(json, '..author') FROM sample;
json_path
"author 0"
"author 1"
"author 2"
(3 rows)
SELECT json_path(json, '..prices[*]') FROM sample;
json_path
1
2
3
4
5
6
7
8
9
(9 rows)
SELECT json_path(json, '..toys[*]') FROM sample;
json_path
"Yo-yo"
"Boomerang"
"Basketball"
(3 rows)
SELECT json_path(json, '..toys..[*]') FROM sample;
json_path
"Yo-yo"
"Boomerang"
"Basketball"
(3 rows)
SELECT json_path(json, '..[-1]') FROM sample;
json_path
(0 rows)
SELECT json_path(json, '..[0]') FROM sample;
json_path
{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}
1
4
7
"Yo-yo"
(5 rows)
SELECT json_path(json, '..[1]') FROM sample;
json_path
{
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}
2
5
8
"Boomerang"
(5 rows)
SELECT json_path(json, '..[2]') FROM sample;
json_path
{
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
3
6
9
"Basketball"
(5 rows)
SELECT json_path(json, '..[3]') FROM sample;
json_path
(0 rows)
SELECT json_path(json, '$') FROM sample;
json_path
{
	"store": {
		"books": [
			{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}, {
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}, {
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
		],
		"toys": [
			"Yo-yo",
			"Boomerang",
			"Basketball"
		]
	}
}
(1 row)
SELECT json_path(json, '..*') FROM sample;
json_path
{
		"books": [
			{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}, {
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}, {
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
		],
		"toys": [
			"Yo-yo",
			"Boomerang",
			"Basketball"
		]
	}
[
			{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}, {
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}, {
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
		]
[
			"Yo-yo",
			"Boomerang",
			"Basketball"
		]
{
				"title": "book 0",
				"author": "author 0",
				"prices": [1,2,3]
			}
{
				"title": "book 1",
				"author": "author 1",
				"prices": [4,5,6]
			}
{
				"title": "book 2",
				"author": "author 2",
				"prices": [7,8,9]
			}
"book 0"
"author 0"
[1,2,3]
1
2
3
"book 1"
"author 1"
[4,5,6]
4
5
6
"book 2"
"author 2"
[7,8,9]
7
8
9
"Yo-yo"
"Boomerang"
"Basketball"
(27 rows)
SELECT json_path(json, '..char(-1)') FROM sample;
json_path
(0 rows)
SELECT json_path(json, '..char(0)') FROM sample;
json_path
"b"
"a"
"b"
"a"
"b"
"a"
"Y"
"B"
"B"
(9 rows)
SELECT json_path(json, '..char(1)') FROM sample;
json_path
"o"
"u"
"o"
"u"
"o"
"u"
"o"
"o"
"a"
(9 rows)
SELECT json_path(json, '..char(2)') FROM sample;
json_path
"o"
"t"
"o"
"t"
"o"
"t"
"-"
"o"
"s"
(9 rows)
SELECT json_path(json, '..char(3)') FROM sample;
json_path
"k"
"h"
"k"
"h"
"k"
"h"
"y"
"m"
"k"
(9 rows)
SELECT json_path(json, '..char(100)') FROM sample;
json_path
(0 rows)
-- Turn aligned output back on.
\a