Re: autogenerated json path

forget previous email, it is solved using function:

create or replace function json_path2txt(data json, jpath varchar) RETURNS
text AS $$
DECLARE
_r record;
_sql varchar;
txt varchar;
BEGIN
_sql:='select data#>>''{'||jpath||'}'' as txt';
-- raise notice '%',_sql ;
EXECUTE(_sql) into txt;
return txt;
END
$$ LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT;

On Thu, Aug 31, 2017 at 11:42 PM, Viktor Bojović <viktor(dot)bojovic(at)gmail(dot)com>
wrote:

> Hi,
> im first time using json data type and i want to autogenerate it, because
> im creating crawler which uses postgresql.
>
> This is static example for one site where path should be automatically
> generate
> SELECT
> file_name
> ,json_data#>>'{1,children,1,children,5,children,3,children,0,children,22,
> children,0,children,1,children,0,content}'
> from src_data
> where file_name ~ 'monitor.hr';
>
> in case that i want to add into table another sites and their paths, i
> would like to write query which automaticaly changes paths.
> SELECT
> file_name
> ,json_data#>>'{''||r.json_path ||''}'
> ,r.json_path
> from src_data d,rules r
> where file_name like r.file_name_prefix||'%';
>
> is there any way to write this without writing functions which would slow
> process?
>
> --
> ---------------------------------------
> Viktor Bojović
>

--
---------------------------------------
Viktor Bojović