Home Explore Blog CI



postgresql

2nd chunk of `doc/src/sgml/json.sgml`
0a7c98c3d6307bc11508b1a0d89cb2f73cefe87490a30fe90000000100000fa3
 database encoding but not
  in UTF8 will be allowed.
 </para>

 <para>
  <acronym>RFC</acronym> 7159 permits JSON strings to contain Unicode escape sequences
  denoted by <literal>\u<replaceable>XXXX</replaceable></literal>.  In the input
  function for the <type>json</type> type, Unicode escapes are allowed
  regardless of the database encoding, and are checked only for syntactic
  correctness (that is, that four hex digits follow <literal>\u</literal>).
  However, the input function for <type>jsonb</type> is stricter: it disallows
  Unicode escapes for characters that cannot be represented in the database
  encoding.  The <type>jsonb</type> type also
  rejects <literal>\u0000</literal> (because that cannot be represented in
  <productname>PostgreSQL</productname>'s <type>text</type> type), and it insists
  that any use of Unicode surrogate pairs to designate characters outside
  the Unicode Basic Multilingual Plane be correct.  Valid Unicode escapes
  are converted to the equivalent single character for storage;
  this includes folding surrogate pairs into a single character.
 </para>

 <note>
  <para>
   Many of the JSON processing functions described
   in <xref linkend="functions-json"/> will convert Unicode escapes to
   regular characters, and will therefore throw the same types of errors
   just described even if their input is of type <type>json</type>
   not <type>jsonb</type>. The fact that the <type>json</type> input function does
   not make these checks may be considered a historical artifact, although
   it does allow for simple storage (without processing) of JSON Unicode
   escapes in a database encoding that does not support the represented
   characters.
  </para>
 </note>

 <para>
  When converting textual JSON input into <type>jsonb</type>, the primitive
  types described by <acronym>RFC</acronym> 7159 are effectively mapped onto
  native <productname>PostgreSQL</productname> types, as shown
  in <xref linkend="json-type-mapping-table"/>.
  Therefore, there are some minor additional constraints on what
  constitutes valid <type>jsonb</type> data that do not apply to
  the <type>json</type> type, nor to JSON in the abstract, corresponding
  to limits on what can be represented by the underlying data type.
  Notably, <type>jsonb</type> will reject numbers that are outside the
  range of the <productname>PostgreSQL</productname> <type>numeric</type> data
  type, while <type>json</type> will not.  Such implementation-defined
  restrictions are permitted by <acronym>RFC</acronym> 7159.  However, in
  practice such problems are far more likely to occur in other
  implementations, as it is common to represent JSON's <type>number</type>
  primitive type as IEEE 754 double precision floating point
  (which <acronym>RFC</acronym> 7159 explicitly anticipates and allows for).
  When using JSON as an interchange format with such systems, the danger
  of losing numeric precision compared to data originally stored
  by <productname>PostgreSQL</productname> should be considered.
 </para>

 <para>
  Conversely, as noted in the table there are some minor restrictions on
  the input format of JSON primitive types that do not apply to
  the corresponding <productname>PostgreSQL</productname> types.
 </para>

  <table id="json-type-mapping-table">
     <title>JSON Primitive Types and Corresponding <productname>PostgreSQL</productname> Types</title>
     <tgroup cols="3">
      <colspec colname="col1" colwidth="1*"/>
      <colspec colname="col2" colwidth="1*"/>
      <colspec colname="col3" colwidth="2*"/>
      <thead>
       <row>
        <entry>JSON primitive type</entry>
        <entry><productname>PostgreSQL</productname> type</entry>
        <entry>Notes</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry><type>string</type></entry>
        <entry><type>text</type></entry>
        <entry><literal>\u0000</literal> is disallowed, as are Unicode escapes
         representing characters not available

Title: JSON Data Type Conversion and Mapping
Summary
The jsonb data type in PostgreSQL has stricter validation and conversion rules compared to the json type, including the handling of Unicode escapes, character encoding, and numeric precision, and there are specific mappings between JSON primitive types and PostgreSQL types, with some restrictions and notes on the input format and data representation.