#1 2021-02-10 08:56:49

nicholas
Member
Registered: 2021-02-10

Retrieve a jsonb object from Postgresql

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

#2 2021-02-10 09:32:12

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Retrieve a jsonb object from Postgresql

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

#3 2021-02-10 11:07:53

nicholas
Member
Registered: 2021-02-10

Re: Retrieve a jsonb object from Postgresql

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

#4 2021-02-10 11:34:28

nicholas
Member
Registered: 2021-02-10

Re: Retrieve a jsonb object from Postgresql

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

eduardo wrote:

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

#5 2021-02-10 14:58:10

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Retrieve a jsonb object from Postgresql

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

#6 2021-02-11 02:36:27

nicholas
Member
Registered: 2021-02-10

Re: Retrieve a jsonb object from Postgresql

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

#7 2021-02-11 08:49:30

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Retrieve a jsonb object from Postgresql

Hi nicholas,

nicholas wrote:

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

#8 2021-02-18 06:00:22

nicholas
Member
Registered: 2021-02-10

Re: Retrieve a jsonb object from Postgresql

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

#9 2021-03-05 15:07:46

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Retrieve a jsonb object from Postgresql

Hi nicholas,

we raised ticket RS-4758 for looking into this.

Regards,
Eduardo

Offline

#10 2021-07-01 06:47:37

nicholas
Member
Registered: 2021-02-10

Re: Retrieve a jsonb object from Postgresql

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

#11 2021-07-08 12:08:06

jalbrecht
Administrator
Registered: 2016-10-21

Re: Retrieve a jsonb object from Postgresql

Dear nicholas,

thank you for sharing your solution for this problem.

wbr

jan

Offline

Board footer

Powered by FluxBB