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

Postgresql – How To Easily Convert A JavaScript Array Into A Postgres BigInt Array

Simple question, but I can’t seem to find a simple answer.

Step 1: I pass this into the postgresql function as json:

{
  "name" : "John Doe",
  "convert" : [32197,4234523,4235423]
}

Step 2: I would LIKE to store the id array within the database ids bigint[] column as {32197,4234523,4235423}

How could I accomplish this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The easiest way I found to do this was:

        SET 
      ids = ARRAY(SELECT json_array_elements_text(d->'convert')::BIGINT)

    Where I am pulling the elements of the array out individually as text objects, then recasting to BIGINT's before pushing them back into an ARRAY


  2. 0

    Try using the function json_to_record.

    Example of usage from the documentation:

    create type myrowtype as (a int, b text);
select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

    In your case:

    SELECT * FROM json_to_record('{"name" : "John Doe", "convert" : [32197,4234523,4235423]}') as x(convert bigint[])

convert         
-------------------------
{32197,4234523,4235423}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search