-SELECT parse_json_path('..');

-SELECT parse_json_path('..*');

-SELECT parse_json_path('.*');

-SELECT parse_json_path('');

-SELECT parse_json_path('$.*');

-SELECT parse_json_path('$');

-SELECT parse_json_path('$*');

-SELECT parse_json_path('*');

-SELECT parse_json_path($$ .. [ 025 ] $$);

-SELECT parse_json_path($$ $ . 0 . 3 $$);

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

-SELECT parse_json_path($$ $ . 0.3 $$);

-SELECT parse_json_path($$ .. [ 031 ] $$);

-SELECT parse_json_path($$ $ . 0 . 3 . hello $$);

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

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

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

-SELECT parse_json_path($$ $ . 0 . 3 . hello . backslash $$);

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

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

-SELECT parse_json_path($$ _3 $$);

-SELECT parse_json_path($$ _3_ $$);

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

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

-SELECT parse_json_path($$ $ . _3 $$);

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

-SELECT parse_json_path('..[5]');

-SELECT parse_json_path('..5');

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

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

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

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

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

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

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

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

-SELECT parse_json_path('."hello"');

-SELECT parse_json_path('.hello');

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

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

-SELECT parse_json_path('."hello world"');

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

-SELECT parse_json_path('.hello world');

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

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

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

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

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

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

-SELECT parse_json_path($$

- .. [ '"hello"\\"world"' ]

- [ 5 ] .. [3] . * [ * ]

-$$);

--- Sample data mainly for testing JSONPath.

--- It'd be nice if it could be moved to a separate file.

-CREATE TABLE sample (json JSON);

-INSERT INTO sample VALUES ($$

-{

- "Query Text": "SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('i') AND substring(pg_catalog.quote_ident(c.relname),1,0)='' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')\nUNION\nSELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,0)='' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,0) = substring('',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1\nUNION\nSELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('i') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,0)='' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,0) = substring('',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,0) = substring('',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1\n UNION SELECT 'ON' UNION SELECT 'CONCURRENTLY'\nLIMIT 1000",

- "Plan": {

- "Node Type": "Limit",

- "Startup Cost": 31.24,

- "Total Cost": 31.29,

- "Plan Rows": 5,

- "Plan Width": 51,

- "Plans": [

- {

- "Node Type": "Aggregate",

- "Strategy": "Hashed",

- "Parent Relationship": "Outer",

- "Startup Cost": 31.24,

- "Total Cost": 31.29,

- "Plan Rows": 5,

- "Plan Width": 51,

- "Plans": [

- {

- "Node Type": "Append",

- "Parent Relationship": "Outer",

- "Startup Cost": 1.07,

- "Total Cost": 31.23,

- "Plan Rows": 5,

- "Plan Width": 51,

- "Plans": [

- {

- "Node Type": "Seq Scan",

- "Parent Relationship": "Member",

- "Relation Name": "pg_class",

- "Alias": "c",

- "Startup Cost": 1.07,

- "Total Cost": 15.04,

- "Plan Rows": 1,

- "Plan Width": 64,

- "Filter": "(pg_table_is_visible(oid) AND (relnamespace <> $2) AND (relkind = 'i'::\"char\") AND (\"substring\"(quote_ident((relname)::text), 1, 0) = ''::text))",

- "Plans": [

- {

- "Node Type": "Seq Scan",

- "Parent Relationship": "InitPlan",

- "Subplan Name": "InitPlan 3 (returns $2)",

- "Relation Name": "pg_namespace",

- "Alias": "pg_namespace",

- "Startup Cost": 0.00,

- "Total Cost": 1.07,

- "Plan Rows": 1,

- "Plan Width": 4,

- "Filter": "(nspname = 'pg_catalog'::name)"

- }

- ]

- },

- {

- "Node Type": "Result",

- "Parent Relationship": "Member",

- "Startup Cost": 1.23,

- "Total Cost": 2.37,

- "Plan Rows": 1,

- "Plan Width": 64,

- "One-Time Filter": "($1 > 1)",

- "Plans": [

- {

- "Node Type": "Aggregate",

- "Strategy": "Plain",

- "Parent Relationship": "InitPlan",

- "Subplan Name": "InitPlan 2 (returns $1)",

- "Startup Cost": 1.21,

- "Total Cost": 1.22,

- "Plan Rows": 1,

- "Plan Width": 64,

- "Plans": [

- {

- "Node Type": "Seq Scan",

- "Parent Relationship": "Outer",

- "Relation Name": "pg_namespace",

- "Alias": "pg_namespace",

- "Startup Cost": 0.00,

- "Total Cost": 1.21,

- "Plan Rows": 1,

- "Plan Width": 64,

- "Filter": "(\"substring\"((quote_ident((nspname)::text) || '.'::text), 1, 0) = \"substring\"(''::text, 1, (length(quote_ident((nspname)::text)) + 1)))"

- }

- ]

- },

- {

- "Node Type": "Seq Scan",

- "Parent Relationship": "Outer",

- "Relation Name": "pg_namespace",

- "Alias": "n",

- "Startup Cost": 0.00,

- "Total Cost": 1.14,

- "Plan Rows": 1,

- "Plan Width": 64,

- "Filter": "(\"substring\"((quote_ident((nspname)::text) || '.'::text), 1, 0) = ''::text)"

- }

- ]

- },

- {

- "Node Type": "Result",

- "Parent Relationship": "Member",

- "Startup Cost": 2.45,

- "Total Cost": 13.75,

- "Plan Rows": 1,

- "Plan Width": 128,

- "One-Time Filter": "($0 = 1)",

- "Plans": [

- {

- "Node Type": "Aggregate",

- "Strategy": "Plain",

- "Parent Relationship": "InitPlan",

- "Subplan Name": "InitPlan 1 (returns $0)",

- "Startup Cost": 1.21,

- "Total Cost": 1.22,

- "Plan Rows": 1,

- "Plan Width": 64,

- "Plans": [

- {

- "Node Type": "Seq Scan",

- "Parent Relationship": "Outer",

- "Relation Name": "pg_namespace",

- "Alias": "pg_namespace",

- "Startup Cost": 0.00,

- "Total Cost": 1.21,

- "Plan Rows": 1,

- "Plan Width": 64,

- "Filter": "(\"substring\"((quote_ident((nspname)::text) || '.'::text), 1, 0) = \"substring\"(''::text, 1, (length(quote_ident((nspname)::text)) + 1)))"

- }

- ]

- },

- {

- "Node Type": "Hash Join",

- "Parent Relationship": "Outer",

- "Join Type": "Inner",

- "Startup Cost": 1.22,

- "Total Cost": 12.51,

- "Plan Rows": 1,

- "Plan Width": 128,

- "Hash Cond": "(c.relnamespace = n.oid)",

- "Join Filter": "(\"substring\"(((quote_ident((n.nspname)::text) || '.'::text) || quote_ident((c.relname)::text)), 1, 0) = ''::text)",

- "Plans": [

- {

- "Node Type": "Seq Scan",

- "Parent Relationship": "Outer",

- "Relation Name": "pg_class",

- "Alias": "c",

- "Startup Cost": 0.00,

- "Total Cost": 10.16,

- "Plan Rows": 100,

- "Plan Width": 68,

- "Filter": "(relkind = 'i'::\"char\")"

- },

- {

- "Node Type": "Hash",

- "Parent Relationship": "Inner",

- "Startup Cost": 1.21,

- "Total Cost": 1.21,

- "Plan Rows": 1,

- "Plan Width": 68,

- "Plans": [

- {

- "Node Type": "Seq Scan",

- "Parent Relationship": "Outer",

- "Relation Name": "pg_namespace",

- "Alias": "n",

- "Startup Cost": 0.00,

- "Total Cost": 1.21,

- "Plan Rows": 1,

- "Plan Width": 68,

- "Filter": "(\"substring\"((quote_ident((nspname)::text) || '.'::text), 1, 0) = \"substring\"(''::text, 1, (length(quote_ident((nspname)::text)) + 1)))"

- }

- ]

- }

- ]

- }

- ]

- },

- {

- "Node Type": "Subquery Scan",

- "Parent Relationship": "Member",

- "Alias": "*SELECT* 4",

- "Startup Cost": 0.00,

- "Total Cost": 0.02,

- "Plan Rows": 1,

- "Plan Width": 0,

- "Plans": [

- {

- "Node Type": "Result",

- "Parent Relationship": "Subquery",

- "Startup Cost": 0.00,

- "Total Cost": 0.01,

- "Plan Rows": 1,

- "Plan Width": 0

- }

- ]

- },

- {

- "Node Type": "Subquery Scan",

- "Parent Relationship": "Member",

- "Alias": "*SELECT* 5",

- "Startup Cost": 0.00,

- "Total Cost": 0.02,

- "Plan Rows": 1,

- "Plan Width": 0,

- "Plans": [

- {

- "Node Type": "Result",

- "Parent Relationship": "Subquery",

- "Startup Cost": 0.00,

- "Total Cost": 0.01,

- "Plan Rows": 1,

- "Plan Width": 0

- }

- ]

- }

- ]

- }

- ]

- }

- ]

- }

-}

-$$);

-

-SELECT md5(from_json(json_path(json, '$."Query Text"'))) FROM sample;

-SELECT json_path($$"⁰¹\u0000³"$$, '[0]');

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

-SELECT json_path($$"⁰¹\u0000³"$$, '[2]');

-SELECT json_path($$"⁰¹\u0000³"$$, '[3]');

-SELECT json_path('"0123"', '[-1]');

-SELECT json_path('"0123"', '[0]');

-SELECT json_path('"0123"', '[1]');

-SELECT json_path('"0123"', '[2]');

-SELECT json_path('"0123"', '[3]');

-SELECT json_path('"0123"', '[4]');

-SELECT json_path('"⁰¹²³"', '[0]');

-SELECT json_path('"⁰¹²³"', '[1]');

-SELECT json_path('"⁰¹²³"', '[2]');

-SELECT json_path('"⁰¹²³"', '[3]');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-

--- 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]', '$..*');

-

-SELECT json_path(json, '.."Node Type"') FROM sample;

-SELECT json_path(json, '.."Total Cost"') FROM sample;

-SELECT json_path(json, '.."Total cost"') FROM sample;

-SELECT json_path(json, '..*') FROM sample;

-SELECT json_path(json, '.Plan."Node Type"') FROM sample;

-SELECT json_path(json, '.Plan.Plans."Node Type"') FROM sample;

-SELECT json_path(json, '.Plan.Plans[*]."Node Type"') FROM sample;

-SELECT json_path(json, '.Plan.Plans[0]."Node Type"') FROM sample;

-SELECT json_path(json, '.Plan.Plans[0].*') FROM sample;

-SELECT json_path(json, '.Plan.Plans[1]."Node Type"') FROM sample;

-SELECT json_path(json, 'Plan') FROM sample;