14 Matching Annotations
  1. Jun 2021
    1. You can use jsonb_agg and jsonb_array_elements_text to flatten or unnest an array of arrays in PostgreSQL: SELECT jsonb_array_elements_text(jsonb_agg(array_of_arrays)) FROM x;
    1. And this has some immediate benefits: more efficiency, significantly faster to process, supports indexing (which can be a significant advantage, as we'll see later), simpler schema designs (replacing entity-attribute-value (EAV) tables with jsonb columns, which can be queried, indexed and joined, allowing for performance improvements up until 1000X!)
    2. Besides efficiency, there are extra ways in which you can benefit from storing JSON in binary form. One such enhancement is the GIN (Generalized Inverted Index) indexes and a new brand of operators that come with them.
    3. SELECT jsonb_array_elements_text(data->'genres') AS genre FROM books WHERE book_id = 1; That will expand the JSON array into a column:
    4. This is an important one, as it will enable us to use the aggregate functions that we are familiar when dealing with relational databases, but in the otherwise counter-intuitive environment of JSON data.
    1. As stated in the title, I am in a situation where I need to return a count of occurrences within an array, that is within a jsonb column.
    1. select t.* from my_table t, jsonb_each(my_col) as value1(key1, value1), jsonb_each(value1) as value2(key2, value2) where jsonb_typeof(my_col) = 'object' and jsonb_typeof(value1) = 'object' and value2->>'Param3' = '6';
    1. json_array_elements_text ( json ) → setof text jsonb_array_elements_text ( jsonb ) → setof text Expands the top-level JSON array into a set of text values. select * from json_array_elements_text('["foo", "bar"]') → value ----------- foo bar