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

Postgresql – Extracting values directly from an array in a JSON text field

I have a piece of JSON which is defined as follows:

declare JSON2 text = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}';

Now I am able to access this directly as follows:

declare SerialNumber2            text;
declare JSON2 text = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}';
SELECT Json2::json->>'SerialNumber'::text into SerialNumber2;

Now that extracts the SerialNumber into a variable

But i now want to extract the contents of ChannelIDs into a temp table defined as follows:

CREATE TEMP TABLE Channel_Info (
       ID                   int NOT NULL,
       ChannelID            INT
      
    );

    INSERT INTO Channel_Info(ID,ChannelID)
    select
           row_number() over (order by ChannelID::text::int ) as ID,
           ChannelID::text::int
    from JSON2
    CROSS JOIN LATERAL json_array_elements(JSON2::json -> 'ChannelIDs') as ChannelID;

But i get the error message relation "JSON2" does not exist

This is my desired output from extracting ChannelsID directly out of the variable JSON2


ID      ChannnelID
1       11
2       12
3       17
4       14
5       15

How i can extract out the values for ChannelIDs?

Tags:

2

Answers


  1. 0

    Your attempt to directly reference the JSON text in the FROM clause of the SELECT statement appears to be the problem with your code; however, the JSON2 variable is not accessible in that scope.

    The variable must be used in a FROM clause referring to a subquery that contains the JSON2 variable. Try this updated code, this creates the desired result by removing the ChannelID values from the JSON text in the JSON2 variable and inserting them into the Channel_Info table:

    CREATE TEMP TABLE Channel_Info (
    ID          int NOT NULL,
    ChannelID   INT
);

INSERT INTO Channel_Info(ID, ChannelID)
SELECT
    row_number() over (order by ChannelID::text::int) as ID,
    ChannelID::text::int
FROM (
    SELECT (json_array_elements(JSON2::json -> 'ChannelIDs')).value::int as ChannelID
    FROM (SELECT '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'::json as JSON2) AS jsonData
) AS Subquery;

    Hope it’s helpful 🙂

    Login or Signup to reply.
  2. 0

    Try this updated code. To directly cast the text representation of the JSON array elements to an integer, I changed .value::int to json_array_elements_text in the updated code. This ought to fix the issue you were having. Let me know if you have any more problems.

        CREATE TEMP TABLE Channel_Info (
    ID          int NOT NULL,
    ChannelID   INT
);

INSERT INTO Channel_Info(ID, ChannelID)
SELECT
    row_number() over (order by ChannelID::text::int) as ID,
    ChannelID::text::int
FROM (
    SELECT json_array_elements_text(JSON2::json -> 'ChannelIDs')::int as ChannelID
    FROM (SELECT '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'::json as JSON2) AS jsonData
) AS Subquery;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search