Retrieve a jsonb object from Postgresql

ReportServer (EN)
1

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

2

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

3

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.

4

I did over simplify the question. Have now sent a more detailed description.

5

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

6

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”}]
7

Hi nicholas,

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

8

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”}’)

9

Hi nicholas,

we raised ticket RS-4758 for looking into this.

Regards,
Eduardo

10

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.

11

Dear nicholas,

thank you for sharing your solution for this problem.

wbr

jan