Extract and display values from JSON array in Select Statement in Postgres

Question

I am developing some code to extract values directly from a text field containing JSON in a JSON array

I want to be able to display the values along with an ID (using Row_Number) but struggling.

Also i want to run this in a simple test harness script without having to create a function with a Return Table statement.

Is this possible ?

OK here is my code:

DO $TESTS$
    DECLARE json text;


BEGIN
    json := '{ "Devices" : [ "894339" , "907578" , "926371", "939029", "940860", "958623", "960232"] }';


 SELECT row_number() OVER (ORDER BY SerialNumber::text) as ID,
        json::json ->> 'Devices' as SerialNumber
 FROM (SELECT JSON::json AS JSON2) AS jsonData; ) AS Subquery;

END;
$TESTS$

And here is the error I get

[2023-11-23 16:18:05] [42601] ERROR: syntax error at or near ")"
[2023-11-23 16:18:05] Position: 320

My desired output is :

ID      SerialNumber
1       894338
2       907578 
3       926371
4       939029
5       940860
6       958623
7       960232

Answer 1

You don't need a temporary table, you can simply select from the output of json_array_elements_text:

SELECT row_number() OVER (ORDER BY value) as ID, value AS SerialNumber
FROM json_array_elements_text(json -> 'Devices');

Output:

id  serialnumber
1   894339
2   907578
3   926371
4   939029
5   940860
6   958623
7   960232

Note as @Bergi points out, you can declare the json value as type json from the beginning. If you don't, you'll need to cast it to type json in the call to json_array_elements_text i.e. json_array_elements_text(json::json -> 'Devices').