Extracting values from JSON in Postgres table

I am trying to extract values from some JSON and getting very stuck on this .

My JSON is as follows:

{
  "SerialNumber":"940860",
  "ChannelIDs":[17,13,11,12,14],
  "BeginningDate":"2023-05-20T11:53:39",
  "EndDate":"2023-05-22T09:04:04"
}

I saved this as a file called spj2.json and then ran the following to load this file in as follows:

CREATE TABLE tmp (c TEXT);

copy tmp from 'C:\temp\spj2.json'

I then tried to query this using the following:

select
    c ->> 'SerialNumber' as SerialNumber,
    c ->> 'BeginningDate' as BeginningDate,
    c ->> 'EndDate' as EndDate
from tmp;

But get the following error message

[2023-11-07 10:54:05] [22P02] ERROR: invalid input syntax for type json
[2023-11-07 10:54:05] Detail: The input string ended unexpectedly.
[2023-11-07 10:54:05] Where: JSON data, line 1: {
[2023-11-07 10:54:05] COPY tmp, line 1, column c: "{"

What I want to do is the following to extract the values from my JSON as follows:

SerialNumber    BeginningDate                EndDate
940860          2023-05-20T11:53:39          2023-05-22T09:04:04

and then the ChannelID values as follows into a local temp table

ID       ChannelID 
1        11
2        12 
3        13 
4        14
5        17

>Solution :

You need to cast the column datatype from text to json :

select
    c::json ->> 'SerialNumber' as SerialNumber,
    c::json ->> 'BeginningDate' as BeginningDate,
    c::json ->> 'EndDate' as EndDate
from tmp;

Result :

serialnumber    beginningdate        enddate
940860          2023-05-20T11:53:39  2023-05-22T09:04:04

Second query can be done using json_array_elements :

select row_number() over (order by ChannelID::text::int ) as ID, ChannelID
from tmp
CROSS JOIN LATERAL json_array_elements(c::json -> 'ChannelIDs') as ChannelID

Result :

id  channelid
1   11
2   12
3   13
4   14
5   17

Demo here