You are not logged in.
Hi all!
This 2021, I have been reviewing for internal use Reportserver 3.3.0 with Postgresql datasource, reports are fine. That is until I came across tables that contain JSON.
How do I extract JSON objects for reports? The TYPE was recognised as VARCHAR and not JSON
Example of a JSON data, how do I extract only the value for "name"?
[{"id":1238,"name":"Apple Pie","value":"1All-1-1000-xzp"}]
I have tried this SQL QUERY but got the error as below;
SELECT p.product -> 'name' FROM products AS p
Query could not be executed: ERROR: operator does not exist: character varying -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 224
Regards,
Nicholas
Last edited by nicholas (2021-02-10 09:03:30)
Offline
Hi Nicholas,
is this a valid postgres query (sending it directly, without ReportServer)? SELECT p.product -> 'name' FROM products AS p
can you pls post a screenshot of the results?
and if you put this query into a dynamic list, what do you see exactly? can you pls post the complete error stack ?
Regards,
Eduardo
Offline
This is specific for Postgresql, and I have prepared the sample as below
Data structure:
CREATE TABLE public.products (
id int4 NOT NULL PRIMARY KEY,
"name" varchar NOT NULL,
product jsonb NULL
);
The data sample:
id|name |product |
--|-----------|----------------------------------------------------------------------|
1|Orange bod |[{"id": 18, "name": "Orange Gala", "value": "1Bll-1-99-aaa"}] |
2|Chicken Pie|[{"id": 4, "name": "Downtown Chicken Pie", "value": "1Bll-1-201-aaa"}]|
3|Apple Pie |[{"id": 5, "name": "Apple Pie", "value": "1All-1-1000-xzp"}] |
The direct SQL query
SELECT name, jsonb_agg(t->'name') as brand, jsonb_agg(t->'value') as code FROM products, jsonb_array_elements(products.product) t
group by name ;
The results
name |brand |code |
-----------|------------------------|-------------------|
Apple Pie |["Apple Pie"] |["1All-1-1000-xzp"]|
Orange bod |["Orange Gala"] |["1Bll-1-99-aaa"] |
Chicken Pie|["Downtown Chicken Pie"]|["1Bll-1-201-aaa"] |
When run sql in Reportserver
Error
Query could not be executed: ERROR: function jsonb_array_elements(character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 653
Is there an alternative method to make that query? One problem I face, not knowing the supported Query functions for JSON in Reportserver.
Last edited by nicholas (2021-02-10 11:25:47)
Offline
I did over simplify the question. Have now sent a more detailed description.
Hi Nicholas,
is this a valid postgres query (sending it directly, without ReportServer)? SELECT p.product -> 'name' FROM products AS p
can you pls post a screenshot of the results?and if you put this query into a dynamic list, what do you see exactly? can you pls post the complete error stack ?
Regards,
Eduardo
Offline
Hi nicholas,
I tried the following (all from within pgadmin):
CREATE TABLE public.products (
id int4 NOT NULL PRIMARY KEY,
"name" varchar NOT NULL,
product jsonb NULL
);insert into public.products (id, name, product) values (1, 'Orange bod', '{"id": 18, "name": "Orange Gala", "value": "1Bll-1-99-aaa"}'')
insert into public.products (id, name, product) values (2, 'Chicken Pie', '{"id": 4, "name": "Downtown Chicken Pie", "value": "1Bll-1-201-aaa"}')
insert into public.products (id, name, product) values (3, 'Apple Pie', '{"id": 5, "name": "Apple Pie", "value": "1All-1-1000-xzp"}')but when I enter this:
SELECT name, jsonb_agg(t->'name') as brand, jsonb_agg(t->'value') as code FROM products, jsonb_array_elements(products.product) t
group by name ;I get:
ERROR: cannot extract elements from an object
SQL state: 22023
Can you pls check?
Regards,
Eduardo
Offline
Firstly, jsonb column should be filled with jsonb format, its not string. Select statement format depends, if you are using multi object or single (this is my case). Here is proper JSON format
[ {"item1","item2"} ]
When you select it should show as below,
SELECT * FROM products;
Results should be
id|name |product |
--|-----------|----------------------------------------------------------------------|
2|Chicken Pie|[{"id": 4, "name": "Downtown Chicken Pie", "value": "1Bll-1-201-aaa"}]|
3|Apple Pie |[{"id": 5, "name": "Apple Pie", "value": "1All-1-1000-xzp"}] |
1|Orange bod |[{"id": 18, "name": "Orange Gala", "value": "1Bll-1-99-aaa"}] |
Last edited by nicholas (2021-02-11 02:36:57)
Offline
Hi nicholas,
Firstly, jsonb column should be filled with jsonb format, its not string.
pls post exactly what I have to enter (e.g. into PGAdmin) in order to enter the data and select it as in your case.
Regards,
Eduardo
Offline
Eduardo,
Thank you for the reply. JSON entry requires the format
[{element1}]
If you change the insert statements, then your select statement will work.
insert into public.products (id, name, product) values (1, 'Orange bod', '[{"id": 18, "name": "Orange Gala", "value": "1Bll-1-99-aaa"}]');
insert into public.products (id, name, product) values (2, 'Chicken Pie', '[{"id": 4, "name": "Downtown Chicken Pie", "value": "1Bll-1-201-aaa"}]');
insert into public.products (id, name, product) values (3, 'Apple Pie', '[{"id": 5, "name": "Apple Pie", "value": "1All-1-1000-xzp"}]');The code you provided, without square brackets is interpreted as String.
insert into public.products (id, name, product) values (3, 'Apple Pie', '{"id": 5, "name": "Apple Pie", "value": "1All-1-1000-xzp"}')
Last edited by nicholas (2021-02-18 06:05:19)
Offline
Hi nicholas,
we raised ticket RS-4758 for looking into this.
Regards,
Eduardo
Offline
Hi,
Thanks for offering the ticket RS-4758, but I am not sure how to follow up with that.
But based on your input, it has provided me a path toward solution that helped me and hope it will help others. This is tested using SQL specific to postgresql database.
SELECT name, t.id as id, t.name as brand, t.value as code
FROM products p,
left join jsonb_to_recordset(p.product::jsonb) as t(id int, name text, value text)
group by name ;
Keep up the good work on the community edition.
Offline
Dear nicholas,
thank you for sharing your solution for this problem.
wbr
jan
Offline