Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table apis ( api_spec varchar(10000));



CREATE TABLE


insert into apis (api_spec) values 
  ('swagger: ''2.0''
info:
  title: "Values"
  version: 2.4.0
  description: "Lorem iosum."
  license:
    name: Copyright
    url: ''https://lorem.com/''
  x-catalogation:
    ba: "Execution"
    bd: "Loans"
    bdd: "Account" 
schemes:
  - "https"
basePath: "/v2/values"
consumes:
  - "application/json"
produces:
  - "application/json"
securityDefinitions:
  JWTProfile:
    type: "oauth"
    description: ''OGT''
    flow: "implicit"
    scopes:
      valueid.read: "lorem"
      valuelist.read: "lorem"
      valuetran.read: "lorem"
      value: "lorem"
      vlimit: "lorem"
      valuestat.read: "lorem"
    authorizationUrl: "$(authorization-url)"
    x-tokenIntrospect:
      url: "$(sca-security-url)"
  ')
;

INSERT 0 1


select unnest
  (regexp_matches(api_spec, 'scopes:\n((?:      [^\n]+\n)+)')) val2 from apis


val2


      valueid.read: "lorem"
      valuelist.read: "lorem"
      valuetran.read: "lorem"
      value: "lorem"
      vlimit: "lorem"
      valuestat.read: "lorem"




SELECT 1


select unnest
  (regexp_matches(t2.val2, '\S+(?=:)','g')) as children from (
select unnest
  (regexp_matches(api_spec, 'scopes:\n((?:      [^\n]+\n)+)')) val2 from apis) as t2


children


valueid.read

valuelist.read

valuetran.read

value

vlimit

valuestat.read



SELECT 6