</entry>

</row>

<row>

<entry><literal>jsonb_hash_ops</></entry>

<entry><literal>jsonb_path_ops</></entry>

<entry><type>jsonb</></entry>

<entry>

<literal>@&gt;</>

<para>

Of the two operator classes for type <type>jsonb</>, <literal>jsonb_ops</>

is the default. <literal>jsonb_hash_ops</> supports fewer operators but

is the default. <literal>jsonb_path_ops</> supports fewer operators but

offers better performance for those operators.

See <xref linkend="json-indexing"> for details.

</para>

</table>

<sect2 id="json-keys-elements">

<title><type>jsonb</> Input and Output Syntax</title>

<title>JSON Input and Output Syntax</title>

<para>

The input/output syntax for the JSON data types is as specified in

<acronym>RFC</> 7159.

<programlisting>

CREATE INDEX idxgin ON api USING gin (jdoc);

</programlisting>

The non-default GIN operator class <literal>jsonb_hash_ops</>

The non-default GIN operator class <literal>jsonb_path_ops</>

supports indexing the <literal>@&gt;</> operator only.

An example of creating an index with this operator class is:

<programlisting>

CREATE INDEXidxginh ON api USING gin (jdocjsonb_hash_ops);

CREATE INDEXidxginp ON api USING gin (jdocjsonb_path_ops);

</programlisting>

</para>

</para>

<para>

Although the <literal>jsonb_hash_ops</literal> operator class supports

Although the <literal>jsonb_path_ops</literal> operator class supports

only queries with the <literal>@&gt;</> operator, it has notable

performance advantages over the default operator

class <literal>jsonb_ops</literal>. A <literal>jsonb_hash_ops</literal>

class <literal>jsonb_ops</literal>. A <literal>jsonb_path_ops</literal>

index is usually much smaller than a <literal>jsonb_ops</literal>

index over the same data, and the specificity of searches is better,

particularly when queries contain keys that appear frequently in the

data. Therefore search operations typically perform better

than with the default operator class.

</para>

<para>

The technical difference between a <literal>jsonb_ops</literal>

and a <literal>jsonb_path_ops</literal> GIN index is that the former

creates independent index items for each key and value in the data,

while the latter creates index items only for each value in the

data.<footnote><para>For this purpose, the term <quote>value</>

includes array elements, though JSON terminology sometimes considers

array elements distinct from values within objects.</para></footnote>

But in <literal>jsonb_path_ops</literal>, each index item is a hash

of both the value and the key(s) leading to it; for example to index

<literal>{"foo": {"bar": "baz"}}</literal>, a single index item would

be created incorporating all three of <literal>foo</>, <literal>bar</>,

and <literal>baz</> into the hash value. Thus a containment query

looking for this structure would result in an extremely specific index

search; but there is no way at all to find out whether <literal>foo</>

appears as a key. On the other hand, a <literal>jsonb_ops</literal>

index would create three index items representing <literal>foo</>,

<literal>bar</>, and <literal>baz</> separately; then to do the

containment query, it would look for rows containing all three of

these items. While GIN indexes can perform such an AND search fairly

efficiently, it will still be less specific and slower than the

equivalent <literal>jsonb_path_ops</literal> search, especially if

there are a very large number of rows containing any single one of the

three index items.

</para>

<para>

A disadvantage of the <literal>jsonb_path_ops</literal> approach is

that it produces no index entries for JSON structures not containing

any values, such as <literal>{"a": {}}</literal>. If a search for

documents containing such a structure is requested, it will require a

full-index scan, which is quite slow. <literal>jsonb_path_ops</> is

therefore ill-suited for applications that often perform such searches.

</para>

<para>

<type>jsonb</> also supports <literal>btree</> and <literal>hash</>

indexes. These are usually useful only if it's important to check

gin_extract_jsonb_hash(PG_FUNCTION_ARGS)

gin_extract_jsonb_path(PG_FUNCTION_ARGS)

{

Jsonb*jb=PG_GETARG_JSONB(0);

int32*nentries= (int32*)PG_GETARG_POINTER(1);

entries= (Datum*)palloc(sizeof(Datum)*total);

tail.parent=NULL;

tail.hash=0;

stack=&tail;

}

gin_extract_jsonb_query_hash(PG_FUNCTION_ARGS)

gin_extract_jsonb_query_path(PG_FUNCTION_ARGS)

{

int32*nentries= (int32*)PG_GETARG_POINTER(1);

StrategyNumberstrategy=PG_GETARG_UINT16(2);

if (strategy!=JsonbContainsStrategyNumber)

elog(ERROR,"unrecognized strategy number: %d",strategy);

entries= (Datum*)

DatumGetPointer(DirectFunctionCall2(gin_extract_jsonb_hash,

DatumGetPointer(DirectFunctionCall2(gin_extract_jsonb_path,

PG_GETARG_DATUM(0),

PointerGetDatum(nentries)));

}

gin_consistent_jsonb_hash(PG_FUNCTION_ARGS)

gin_consistent_jsonb_path(PG_FUNCTION_ARGS)

{

bool*check= (bool*)PG_GETARG_POINTER(0);

StrategyNumberstrategy=PG_GETARG_UINT16(1);

elog(ERROR,"unrecognized strategy number: %d",strategy);

*recheck= true;

for (i=0;i<nkeys;i++)

}

gin_triconsistent_jsonb_hash(PG_FUNCTION_ARGS)

gin_triconsistent_jsonb_path(PG_FUNCTION_ARGS)

{

GinTernaryValue*check= (GinTernaryValue*)PG_GETARG_POINTER(0);

StrategyNumberstrategy=PG_GETARG_UINT16(1);

DATA(insert (4033380238025s32434030 ));

DATA(insert (4034380238021s32404050 ));

DATA(insert (4036380238027s324627420 ));

DATA(insert (40363802259s324727420 ));

DATA(insert (40363802100910s324827420 ));

DATA(insert (40363802100911s324927420 ));

DATA(insert (4037380238027s324627420 ));

DATA(insert (403jsonb_opsPGNSPPGUID40333802t0 ));

DATA(insert (405jsonb_opsPGNSPPGUID40343802t0 ));

DATA(insert (2742jsonb_opsPGNSPPGUID40363802t25 ));

DATA(insert (2742jsonb_hash_opsPGNSPPGUID40373802f23 ));

DATA(insert (2742jsonb_path_opsPGNSPPGUID40373802f23 ));

DATA(insertOID=4015 (4000quad_point_opsPGNSPPGUID ));

DATA(insertOID=4016 (4000kd_point_opsPGNSPPGUID ));

DATA(insertOID=4017 (4000text_opsPGNSPPGUID ));

#defineTEXT_SPGIST_FAM_OID 4017

DATA(insertOID=4033 (403jsonb_opsPGNSPPGUID ));

DATA(insertOID=4034 (405jsonb_opsPGNSPPGUID ));

DATA(insertOID=4035 (783jsonb_opsPGNSPPGUID ));

DATA(insertOID=4036 (2742jsonb_opsPGNSPPGUID ));

DATA(insertOID=4037 (2742jsonb_hash_opsPGNSPPGUID ));

#defineTEXT_SPGIST_FAM_OID 4017

DATA(insertOID=4037 (2742jsonb_path_opsPGNSPPGUID ));

DESCR("GIN support");

DATA(insertOID=3488 (gin_triconsistent_jsonbPGNSPPGUID121000fffftfi7016"2281 21 2277 23 2281 2281 2281"_null__null__null__null_gin_triconsistent_jsonb_null__null__null_ ));

DESCR("GIN support");

DATA(insert OID = 3485 (gin_extract_jsonb_hash PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 2281 "2281 2281 2281" _null_ _null_ _null_ _null_gin_extract_jsonb_hash _null_ _null_ _null_ ));

DATA(insertOID=3485 (gin_extract_jsonb_pathPGNSPPGUID121000fffftfi302281"2281 2281 2281"_null__null__null__null_gin_extract_jsonb_path_null__null__null_ ));

DESCR("GIN support");

DATA(insert OID = 3486 (gin_extract_jsonb_query_hashPGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 2281 "2277 2281 21 2281 2281 2281 2281" _null_ _null_ _null_ _null_gin_extract_jsonb_query_hash _null_ _null_ _null_ ));

DATA(insertOID=3486 (gin_extract_jsonb_query_pathPGNSPPGUID121000fffftfi702281"2277 2281 21 2281 2281 2281 2281"_null__null__null__null_gin_extract_jsonb_query_path_null__null__null_ ));

DESCR("GIN support");

DATA(insert OID = 3487 (gin_consistent_jsonb_hash PGNSP PGUID 12 1 0 0 0 f f f f t f i 8 0 16 "2281 21 2277 23 2281 2281 2281 2281" _null_ _null_ _null_ _null_gin_consistent_jsonb_hash _null_ _null_ _null_ ));

DATA(insertOID=3487 (gin_consistent_jsonb_pathPGNSPPGUID121000fffftfi8016"2281 21 2277 23 2281 2281 2281 2281"_null__null__null__null_gin_consistent_jsonb_path_null__null__null_ ));

DESCR("GIN support");

DATA(insert OID = 3489 (gin_triconsistent_jsonb_hashPGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 16 "2281 21 2277 23 2281 2281 2281" _null_ _null_ _null_ _null_gin_triconsistent_jsonb_hash _null_ _null_ _null_ ));

DATA(insertOID=3489 (gin_triconsistent_jsonb_pathPGNSPPGUID121000fffftfi7016"2281 21 2277 23 2281 2281 2281"_null__null__null__null_gin_triconsistent_jsonb_path_null__null__null_ ));

DESCR("GIN support");

externDatumjsonb_cmp(PG_FUNCTION_ARGS);

externDatumjsonb_hash(PG_FUNCTION_ARGS);

externDatumgin_compare_jsonb(PG_FUNCTION_ARGS);

externDatumgin_extract_jsonb(PG_FUNCTION_ARGS);

externDatumgin_extract_jsonb_query(PG_FUNCTION_ARGS);

externDatumgin_consistent_jsonb(PG_FUNCTION_ARGS);

externDatumgin_triconsistent_jsonb(PG_FUNCTION_ARGS);

externDatumgin_extract_jsonb_hash(PG_FUNCTION_ARGS);

externDatumgin_extract_jsonb_query_hash(PG_FUNCTION_ARGS);

externDatumgin_consistent_jsonb_hash(PG_FUNCTION_ARGS);

externDatumgin_triconsistent_jsonb_hash(PG_FUNCTION_ARGS);

externDatumgin_extract_jsonb_path(PG_FUNCTION_ARGS);

externDatumgin_extract_jsonb_query_path(PG_FUNCTION_ARGS);

externDatumgin_consistent_jsonb_path(PG_FUNCTION_ARGS);

externDatumgin_triconsistent_jsonb_path(PG_FUNCTION_ARGS);

externintcompareJsonbContainers(JsonbContainer*a,JsonbContainer*b);

1

(1 row)

--ginhash

--ginpath opclass

DROP INDEX jidx;

CREATE INDEX jidx ON testjsonb USING gin (jjsonb_hash_ops);

CREATE INDEX jidx ON testjsonb USING gin (jjsonb_path_ops);

SET enable_seqscan = off;

SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';

count

1

(1 row)

--ginhash

--ginpath opclass

DROP INDEX jidx;

CREATE INDEX jidx ON testjsonb USING gin (jjsonb_hash_ops);

CREATE INDEX jidx ON testjsonb USING gin (jjsonb_path_ops);

SET enable_seqscan = off;

SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';

count

SELECTcount(*)FROM testjsonbWHERE j>'{"p":1}';

SELECTcount(*)FROM testjsonbWHERE j='{"pos":98, "line":371, "node":"CBA", "indexed":true}';

DROPINDEX jidx;

CREATEINDEXjidxON testjsonb USING gin (jjsonb_hash_ops);

CREATEINDEXjidxON testjsonb USING gin (jjsonb_path_ops);

SET enable_seqscan= off;

SELECTcount(*)FROM testjsonbWHERE j @>'{"wait":null}';