Simplify a jsonb request



  • I am a newbie in request involving jsonb.

    Can this one be improved? collections is a huge jsonb field and maybe one cross join is enough.

    SELECT actions
    FROM layouts
             CROSS JOIN jsonb_array_elements(elements) AS element
             CROSS JOIN jsonb_array_elements(element.value->'sub'->'actions') as actions
    WHERE id = 124350001
      AND actions->>'id' = '1234'
      AND "deletedAt" IS NULL;
    

    Here is an example of the "elements" field value:

    {
      "sub": { "actions": [{"id":"1234", "name": "one"},{"id":"45678", name: 'two'}] }
    }
    

    The request should return an action, for example:

    {"id":"1234", "name": "one"}
    


  • You can use a JSON path query, to extract that element from the column's value:

    select jsonb_path_query_first(elements, '$.sub.actions[*] ? (@.id == "1234")') as actions
    from layouts
    WHERE id = 124350001
      AND "deletedAt" IS NULL;
    

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=23e34e205998d28cbb22499213d3e6b7




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2