In my postgresql function come parameter data in json format

{"name": ["Петя", "Коля", "Вася"]} .

There is a table with names from which to select the class

 SELECT age, address FROM users WHERE name IN (... а вот тут как-то вставить мой входящий parameter->>'name' ...)

Tell me how to do this syntactically and ideologically correctly in order not to arrange unnecessary preliminary parsing of strings inside the function, unnecessary conversions and so on. thank

    1 answer 1

    You can expand the json array into the list:

     where name in ( select n from json_array_elements_text(parameter->'name') as n )

    Or jsonb_array_elements_text , depending on the type of your argument.

    It is also possible to use a regular operator to find the entry of a string in the jsonb array:

     where parameter->'name' ? name

    One problem is that such a record does not use an index by name , if there is one. Unlike the subquery with json_array_elements_text.

    • thanks for the answer. The solution turned out to be really simple SELECT.... IN (select value from json_array_elements_text( (parameter->>'name') ) well, with a preliminary type conversion, but these are details. Thanks! - Ilya Bazhinov
    • 2
      pay attention to -> instead of ->> , then type casting is not necessary to call a function. - Shallow
    • By the way, yes, thanks for the correction! - Ilya Bazhinov