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;

jsonb_pretty

That's going to be extremely ugly. Nothing about this makes sense. Your JSON schema should just have one object that has {"is_enabled":true}, or something like this {"name":"change","is_enable":true}.

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!)

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.

SELECT jsonb_array_elements_text(data->'genres') AS genre FROM books WHERE book_id = 1; That will expand the JSON array into a column:

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.

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.

SELECT DISTINCT ON (1) t.id, t.name, d.last FROM tbl t LEFT JOIN LATERAL json_array_elements_text(data) WITH ORDINALITY d(last, rn) ON d.last <> t.name ORDER BY d.rn DESC;

jsonb_each_text

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';

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

Use the -> operator instead of ->> in the reference to children. The way you have it, you'd first cast json / jsonb to text and then back to json.