PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Extract part of keys from JSON column

I have this table column:

sql json column

I want the trailing numbers retrieved from the key names and stored in this manner:

ID
1
2
3
4

I can retrieve it using:

REGEXP_REPLACE((json_each_text(fixtures::json)).key, '.*?(d+)', '1')::int as ID

But I want it retrieved differently without '.*?(d+)', '1')

Tags:

2

Answers


  1. 0
    1. Use json_object_keys() instead of extracting key:value pairs then only keeping .key field.
    2. You can extract only the matching subexpression with regexp_substr() instead of matching the whole thing, then replacing it with the parenthesized part.
    3. $ matches the end of the input string.

    Demo at db<>fiddle:

    create table your_table(fixtures) as values
('{ "fixture_1":"fixture1"
   ,"fixture_2":"fixture2"
   ,"fixture_321":"fixture321"}');

select regexp_substr(json_object_keys(fixtures::json),'(d+)$')::int as "ID"
from your_table;
    ID
    1
    2
    321
    Login or Signup to reply.
  2. 0

    json_object_keys() is simpler and faster for the case than json_each_text(), Zegarek already provided that.

    split_part() is simpler and faster for the case (always one _ as separator before the dangling number) than employing regular expressions. You seem to be asking for a solution without regexp: "without '.*?(d+)', '1')".

    split_part(json_object_keys(fixtures::json), '_', 2)::int AS id

    See:

    Also, fixtures should probably be type json or jsonb to begin with, making the cast ::json unnecessary.

    You are aware that this produces a single set of from all top-level object keys, with possible duplicates? You may want to throw in DISTINCT:

    SELECT DISTINCT split_part(json_object_keys(fixtures), '_', 2)::int AS id
FROM   tbl
ORDER  BY id;

    Or you may want IDs per row (no dupes per row if your key names are consistent):

    SELECT t.id, k.ids
FROM   tbl t
LEFT   JOIN LATERAL (
  SELECT ARRAY (SELECT split_part(json_object_keys(t.fixtures), '_', 2)::int)
  ) k(ids) ON true;

    fiddle

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search