Question 185026 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Conversion of JSON array with ::text[] not working as expected

I’m using ::text[] to convert an array inside a JSON column, like in the example below:

Imagine sometable to have a JSON column called ‘object’ with a structure like the following:

{ "properties": ["string", "someotherstring"] }

I am querying the db like this:

SELECT * FROM db.sometable
WHERE 'string' = ANY (ARRAY[object -> 'properties']::text[]);

I am expecting ARRAY[object -> 'properties']::text[] to return some kind of list of strings, like the following:

{"string", "someotherstring"}

…but, after some debugging, I realized it’s returning some wierd s*** like the following:

{"["string", "someotherstring"]"}

Basically, it is treating the whole array as a string! What could be the cause?

PS: I understand that I should use other functions or filters, but the real query I’m working on is highly dynamic and way more complicated than the example I provided. ::text[] seems to be the only option I have

Thank you in advance for your help!

I am expecting ::text[] to convert a JSON array into a list of strings, but it is actually considering the whole array as a string.

Tags:

3

Answers


  1. 0

    Use json_array_elements_text combined with ARRAY to convert a json array into list of text values TEXT[] :

    SELECT * 
FROM sometable
WHERE 'string' = ANY (ARRAY(SELECT json_array_elements_text(object->'properties')));

    This is another method for looking for an element within a json array :

    SELECT s.*
FROM sometable s, json_array_elements_text(object->'properties') as j
where j.value = 'string'

    Demo here

    Login or Signup to reply.
  2. 0

    Hope this solves your problem

    The table content: a screenshot of the table content

    SELECT string_to_array(element, ',') AS string_array
FROM (
  SELECT json_array_elements_text(object -> 'properties') AS element
  FROM untitled_table_225
) subquery;

    it gives the following output:

    a screenshot of the solution output

    As you can see the records contain "{" and "}"; I simply removed them using regexp_replace function.

    The SQL now being like this:

    SELECT regexp_replace(string_to_array(element, ',')::text, '}|{', '', 'g') AS string_array
FROM (
  SELECT json_array_elements_text(object -> 'properties') AS element
  FROM untitled_table_225
) subquery;

    The results: a screenshot of the final results

    Login or Signup to reply.
  3. 0

    There is no reason to convert the json to a Postgres array. You need a proper operator.

    select * 
from sometable
where object -> 'properties' ? 'string';

    Test it in db<>fiddle.

    Read about json functions and operators in the documentation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search