CODEWITHSUNDEEP
postgresql
rulans
rulans

Reputation: 23

How to work with data values formatted [{}, {}, {}]

I apologize if this is a simple question - I had some trouble even formatting the question when I was trying to Google for help!

In one of the tables I am working with, there's data value that looks like below:

Invoice ID Status Product List
1234 Processed [{"product_id":463153},{"product_id":463165},{"product_id":463177},{"pid":463218}]

I want to count how many products each order has purchased. What is the proper syntax and way to count the values under "Product List" column? I'm aware that count() is wrong, and I need to maybe extract the data from the string value.

select invoice_id, count(Product_list) 

from quote_table

where status = 'processed'

group by invoice_id

Upvotes: 0

Views: 41

Answers (1)

Anthony Sotolongo
Anthony Sotolongo

Reputation: 1648

You can use a JSON function named: json_array_length and cast this column like a JSON data type (as long as possible), for example:


select invoice_id, json_array_length(Product_list::json)  as count

from quote_table

where status = 'processed'

group by invoice_id;


 invoice_id | count 
------------+-------
     1234   |     4
(1 row)

Upvotes: 1

Related Questions