Home • Explore • Blog • CI
☰



postgresql
6th chunk of `doc/src/sgml/hstore.sgml`
fcc2ca2629c8f19a0db19cc3c92f3874ef6e010246c6a4e70000000100000fa7
 <function>hstore_to_array</function> ( <type>hstore</type> )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s keys and values as an array of
        alternating keys and values.
       </para>
       <para>
        <literal>hstore_to_array('a=&gt;1,b=&gt;2')</literal>
        <returnvalue>{a,1,b,2}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_matrix</primary></indexterm>
        <function>hstore_to_matrix</function> ( <type>hstore</type> )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s keys and values as a two-dimensional
        array.
       </para>
       <para>
        <literal>hstore_to_matrix('a=&gt;1,b=&gt;2')</literal>
        <returnvalue>{{a,1},{b,2}}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_json</primary></indexterm>
        <function>hstore_to_json</function> ( <type>hstore</type> )
        <returnvalue>json</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>json</type> value,
        converting all non-null values to JSON strings.
       </para>
       <para>
        This function is used implicitly when an <type>hstore</type> value is
        cast to <type>json</type>.
       </para>
       <para>
        <literal>hstore_to_json('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal>
        <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_jsonb</primary></indexterm>
        <function>hstore_to_jsonb</function> ( <type>hstore</type> )
        <returnvalue>jsonb</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>jsonb</type> value,
        converting all non-null values to JSON strings.
       </para>
       <para>
        This function is used implicitly when an <type>hstore</type> value is
        cast to <type>jsonb</type>.
       </para>
       <para>
        <literal>hstore_to_jsonb('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal>
        <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_json_loose</primary></indexterm>
        <function>hstore_to_json_loose</function> ( <type>hstore</type> )
        <returnvalue>json</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>json</type> value, but
        attempts to distinguish numerical and Boolean values so they are
        unquoted in the JSON.
       </para>
       <para>
        <literal>hstore_to_json_loose('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal>
        <returnvalue>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_jsonb_loose</primary></indexterm>
        <function>hstore_to_jsonb_loose</function> ( <type>hstore</type> )
        <returnvalue>jsonb</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>jsonb</type> value, but
        attempts to distinguish numerical and Boolean values so they are
        unquoted
Title: hstore to JSON/JSONB Conversion Functions in PostgreSQL
Summary
This section describes functions for converting hstore data type to JSON and JSONB formats in PostgreSQL. The functions include hstore_to_json and hstore_to_jsonb, which convert hstore to JSON and JSONB respectively, treating all non-null values as JSON strings. These functions are implicitly used when casting hstore to JSON or JSONB. Additionally, hstore_to_json_loose and hstore_to_jsonb_loose attempt to distinguish numerical and Boolean values, leaving them unquoted in the resulting JSON or JSONB. Examples are provided for each function, demonstrating how different hstore key-value pairs are converted, including handling of special characters, null values, and various data types. These functions enhance the interoperability between hstore and JSON/JSONB data types in PostgreSQL.