Re: Querying JSON Lists

"Sven R. Kunze" <srkunze@xxxxxxx> · Fri, 3 Mar 2017 20:50:40 +0100



    On 03.03.2017 16:05, Adrian Klaver
        wrote:

      
    https://www.postgresql.org/docs/9.6/static/functions-json.html
      

      As to why it works on JSON arrays:
      

      Table 9-43. Additional jsonb Operators
      

      "
      

      ?     text     Does the string exist as a top-level key
        within the JSON value?
      

      "
      

      So to be picky it not does call out JSON object it says
        JSON value. And right above the table:
      

      " For a full description of jsonb containment and
        existence semantics, see Section 8.14.3. Section 8.14.4
        describes how these operators can be used to effectively index
        jsonb data."
      

      As to how that behavior was decided on I have no idea, it
        just is.
      

    I think it would even be possible to add the
      integer-variant of the ? operator.

      
      Something I learned right now: integers cannot be object keys in
      json. On the flip side, they can be array elements. So, I can see
      a certain logic because of a uncertainty of integers.

      
      Python differs here from PostgreSQL:

      
      >>> json.dumps({4: '34'})

      '{"4": "34"}'

      
      >>>># select '{4:4}'::jsonb;

      ERROR:  invalid input syntax for type json

      LINE 1: select '{4:4}'::jsonb;

                     ^

      DETAIL:  Expected string or "}", but found "4".

      CONTEXT:  JSON data, line 1: {4...

      
      Python wraps it up, PostgreSQL fails loudly. Not that PostgreSQL
      is confused by Python, but it's weird when ? operator works on
      both keys and arrays with text but works only on arrays with
      integers. So, I guess no support for integers for now might have
      seen like a good idea.

    
      Though there looks to be some implicit casting going on:
      

      test=> select '["12","34","45"]'::jsonb @>
        '"12"'::text; 

      
      ERROR:  operator does not exist: jsonb @> text 

      
      LINE 1: select '["12","34","45"]'::jsonb @>
        '"12"'::text;
      

      to get '"12"' to be '"12"'::jsonb.
      

      As to why, I don't know.

      
    This makes sense to me at least, as we test structural json
      containment. So, testing json to be contained by other json
      requires it to be json. :)

    
    The confusing fact is that one can omit the array brackets
      in case of a single primitive value. Don't get me wrong. I don't
      complain as it's a usability feature. However I didn't expect it
      to be there in the first place and adding some brackets wouldn't
      hurt IMO. I'd rather consider brackets a readability support such
      as "this is json".

    
    Sven