How to parse a table with a JSON array field in PostgreSQL into rows?

Question

I have a table that contains a json array. Here is a sample of the contents of the field from:

SELECT json_array FROM table LIMIT 5;

Result:

[{"key1":"value1"}, {"key1":"value2"}, ..., {"key2":"value3"}]
[]
[]
[]{"key1":"value1"}
[]

How can I retrieve all the values and count how many of each value was found?

I am using PostgreSQL 9.5.14, and I have tried the solutions here Querying a JSON array of objects in Postgres and the ones suggested to me by another generous stackoverflow user in my last question: How can I parse JSON arrays in postgresql?

I tried:

SELECT 
    value -> 'key1'
FROM
    table,
    json_array_elements(json_array);

which sadly does not work for me due to receiving the error:
cannot call json_array_elements on a scalar
This error happens when using a query that returns more than one row or more than one column as a scalar subquery.

Another solution I tried was:

SELECT json_array as json, (json_array->0), 
  coalesce(
    case
      when (json_array->0) IS NULL then null
      else (json_array->0->>'key1')
    end,
  'No value') AS "Value"
FROM table;

which only returned null values for the "Value"

Referencing Querying a JSON array of objects in Postgres I attempted to use this solution as well:

WITH json_test (col) AS (
values (json_arrays)
)
SELECT
  y.x->'key1' "key1"
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y;

But I would need to be able to fit all the elements of the json_arrays into json_test

So far I have only attempted to list all the values in the all json arrays, but my ideal end-result for the query resembles this:

Yet again I am grateful for any help with this, thank you in advance.

I figured out the error. It was due to our json arrays in the table being saved with quotation marks around them. This prevented using any of postgresql's native json-functions on the columns data, and string manipulation was not possible without typecasting the broken json arrays first to text. I was using the sql lab tool to construct my sql queries, which didn't show the quotation marks, and lead me to believe the reason for the error was elsewhere. — Lauri
– Lauri, Commented May 27, 2019 at 19:08

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

step-by-step demo:db<>fiddle

SELECT
    each.value,
    COUNT(*)
FROM
    data,
    json_array_elements(json_array) elems,   -- 1
    json_each_text(elems) each               -- 2
GROUP BY each.value                          -- 3

Expand array into one row for each array element
split the key/value pairs into two columns
group by the new value column/count

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 23, 2019 at 12:34

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lauri Over a year ago

Thank you again. Unfortunately this returns the error: "cannot call json_array_elements on a scalar".

S-Man Over a year ago

@Lauri Please check the demo and have a look what is different in your case. In my case this obviously works. Maybe your table structure is a bit different?

S-Man Over a year ago

@Lauri Maybe this helps? stackoverflow.com/questions/31708851/…

Lauri Over a year ago

Thank you, this also returns "cannot call json_object_keys on a scalar". There must be some fault at my end that I haven't noticed. I am going to try to replicate my issue on fiddle and I should also go through my json column to see if there is any abnormalities causing this. I keep getting these "cannot call <function> on a scalar" messages even when I end my query with limit 1 and query an empty [] json array with the selects you created. I also double checked that the type of the column is json. Thank you for all your help!

Lauri Over a year ago

Ah no, your fiddle works everytime. I will try to see if I can recreate my error in fiddle. I will post a solution if I can find what causes this.

Collectives™ on Stack Overflow

How to parse a table with a JSON array field in PostgreSQL into rows?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related