Ygthkb

How to limit sight distance to 1 km

Which one of these password policies is more secure?

The weather forecast

awk + sum all numbers

How would an AI self awareness kill switch work?

Can we use the stored gravitational potential energy of a building to produce power?

Consequences of lack of rigour

Why has the mole been redefined for 2019?

Why would space fleets be aligned?

Am I a Rude Number?

Vertical alignment of rbrace

Why is mind meld hard for T'pol in Star Trek: Enterprise?

Why avoid shared user accounts?

Why is the copy constructor called twice in this code snippet?

Incorporating research and background: How much is too much?

Explain the objections to these measures against human trafficking

Can I string the D&D Starter Set campaign into another module, keeping the same characters?

How do Chazal know that the descendants of a Mamzer may never marry into the general populace?

Publishing research using outdated methods

Finding a mistake using Mayer-Vietoris

How to say "Brexit" in Latin?

Can an insurance company drop you after receiving a bill and refusing to pay?

Eww, those bytes are gross

Can a person refuse a presidential pardon?

PostgreSQL select jsonb key value from an array while joining table

MySQL : LEFT JOIN not fetching required dataPostgreSQL joining using JSONBPostgreSql JSONB SELECT against multiple valuesHow to get particular object from jsonb array in PostgreSQL?How to return value for key from a jsonb column?postgresql: how to define a JSONB column with default valuePostgreSQL 9.5 query performance depends on JOINed column in SELECT clausePostgreSQL JSONB update array elements queryFilter rows by objects in jsonb array in PostgreSQLJSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

0

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

tables:

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

example of join_table.arr

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

What I am trying to achieve is to get concat a comma separated string of all the join_table.arr Name values

postgresql join json

share

asked 3 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

tables:

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

example of join_table.arr

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

What I am trying to achieve is to get concat a comma separated string of all the join_table.arr Name values

postgresql join json

share

asked 3 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

tables:

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

example of join_table.arr

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

What I am trying to achieve is to get concat a comma separated string of all the join_table.arr Name values

postgresql join json

share

asked 3 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

share

asked 3 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 3 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

gadelkareemgadelkareem

1011